package com.steering.mes.zgmil.coup;

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

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.MilSawResultDeatilEntity;

public class PipSawFast extends DBCommon {

	public CoreReturnObject doQuerySlabCount(String jugeNo, String proPlanId,
			String gxPlanNo) {
		return this.getDataBySqlID("PipSawFast.doQuerySlabCount", new Object[] {
				jugeNo, proPlanId, gxPlanNo, jugeNo, proPlanId, gxPlanNo });
	}

	/**
	 * 获取管号队列支数（排除被剔除的）
	 * 
	 * @param heatno
	 * @return
	 */
	public CoreReturnObject GetCountWithoutDel(String heatno, String proPlanId,
			String gxPlanNo) {
		String sql = "Select count(*)AS counts from MIL_SAW_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.status=0";
		sql += " AND t.ROW_NO  is null";

		return this.getData(sql);
	}

	public CoreReturnObject GetCountHasSaw(String heatno, String proPlanId,
			String gxPlanNo) {
		String sql = "Select count(*)AS counts from MIL_SAW_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.status=0";
		sql += " AND t.ROW_NO  is not null";
		return this.getData(sql);
	}

	/**
	 * 1、插入sawdeatail 2、更新管号队列
	 * 
	 * @param milsawdeatail
	 * @return
	 */
	public CoreReturnObject InsertSawDetail(
			MilSawResultDeatilEntity milsawdeatail) {
		ArrayList<String> sqllist = new ArrayList<String>();
		// 插入sawdeatail
		String sql = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.Addsawdeatail");
		// 更新管号队列
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.UpdateSawRack");
		sql = EntityTool.fillSqlByEntity(sql, milsawdeatail);
		sql1 = EntityTool.fillSqlByEntity(sql1, milsawdeatail);
		sqllist.add(sql);
		sqllist.add(sql1);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	public CoreReturnObject InsertSawDetailNEW(
			MilSawResultDeatilEntity milsawdeatail) throws SQLException {

		String sql = "";
		ArrayList<String> sqllist = new ArrayList<String>();

		// 插入sawdeatail

		sql = CoreXmlSqlParsersFactory.getSql("PipSawFast.AddsawdeatailNEW");
		// 更新管号队列
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.UpdateSawRackNEW");
		// sql3 = EntityTool.fillSqlByEntity(sql3, milsawdeatail);
		sql = EntityTool.fillSqlByEntity(sql, milsawdeatail);
		sql1 = EntityTool.fillSqlByEntity(sql1, milsawdeatail);
		// sqllist.add(sql3);
		sqllist.add(sql);
		sqllist.add(sql1);
		String sql2 = "UPDATE MIL_SAW_RESULT T SET T.CUT_HEAD_LENGTH= "
				+ milsawdeatail.getCutHeadLength()
				+ " , CUT_SPEED = '"+milsawdeatail.getCutSpeed()+"'"
				+ " , CUT_THICKNESS = '"+milsawdeatail.getCutThickness()+"'"
				+ " WHERE T.JUDGE_STOVE_NO='";
		sql2 += milsawdeatail.getJudgeStoveNo()
				+ "' AND NVL(PRO_PLAN_ID,'/') = NVL('"
				+ milsawdeatail.getProPlanId()
				+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('"
				+ milsawdeatail.getGxPlanNo() + "','/')";
		sqllist.add(sql2);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	// 修改分切详细
	public CoreReturnObject updateSawDetailNEW(
			MilSawResultDeatilEntity milsawdeatail) throws SQLException {

		String sql = "";
		ArrayList<String> sqllist = new ArrayList<String>();

		// 插入sawdeatail

		sql = CoreXmlSqlParsersFactory.getSql("PipSawFast.UpdatesawdeatailNEW");
		// 更新管号队列
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.UpdateSawRackNEW1");
		// sql3 = EntityTool.fillSqlByEntity(sql3, milsawdeatail);
		sql = EntityTool.fillSqlByEntity(sql, milsawdeatail);
		sql1 = EntityTool.fillSqlByEntity(sql1, milsawdeatail);
		// sqllist.add(sql3);
		sqllist.add(sql);
		sqllist.add(sql1);
		String sql2 = "UPDATE MIL_SAW_RESULT T SET T.CUT_HEAD_LENGTH= "
				+ milsawdeatail.getCutHeadLength()
				+ " , CUT_SPEED = '"+milsawdeatail.getCutSpeed()+"'"
				+ " , CUT_THICKNESS = '"+milsawdeatail.getCutThickness()+"'"
				+ " WHERE T.JUDGE_STOVE_NO='";
		sql2 += milsawdeatail.getJudgeStoveNo()
				+ "' AND NVL(PRO_PLAN_ID,'/') = NVL('"
				+ milsawdeatail.getProPlanId()
				+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('"
				+ milsawdeatail.getGxPlanNo() + "','/')";
		sqllist.add(sql2);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	public CoreReturnObject doQueryMilSawDeatil(
			MilSawResultDeatilEntity milsawdeatail) {
		String sql = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.SelectsawdeatailNEW");
		sql = EntityTool.fillSqlByEntity(sql, milsawdeatail);
		return this.getData(sql);
	}

	public CoreReturnObject InsertSawDetailNEWBACK(
			MilSawResultDeatilEntity milsawdeatail) {
		ArrayList<String> sqllist = new ArrayList<String>();
		// 插入sawdeatail
		String sql = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.AddsawdeatailNEWBACK");
		// 更新管号队列
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.UpdateSawRackNEWBACK");
		sql = EntityTool.fillSqlByEntity(sql, milsawdeatail);
		sql1 = EntityTool.fillSqlByEntity(sql1, milsawdeatail);
		sqllist.add(sql);
		sqllist.add(sql1);

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

	public CoreReturnObject UPDATEORDER_LENGTH(
			MilSawResultDeatilEntity milsawdeatail) {
		ArrayList<String> sqllist = new ArrayList<String>();
		String sql = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.UpdatesawdeatailOrder");
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.UpdateSawRackOrder");
		sql = EntityTool.fillSqlByEntity(sql, milsawdeatail);
		sql1 = EntityTool.fillSqlByEntity(sql1, milsawdeatail);
		sqllist.add(sql);
		sqllist.add(sql1);
		String sql2 = "UPDATE MIL_SAW_RESULT T SET T.ORDER_LENGTH= "
				+ milsawdeatail.getOrderLength() + " WHERE T.JUDGE_STOVE_NO='";
		sql2 += milsawdeatail.getJudgeStoveNo()
				+ "' AND NVL(PRO_PLAN_ID,'/') = NVL('"
				+ milsawdeatail.getProPlanId()
				+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('"
				+ milsawdeatail.getGxPlanNo() + "','/')";
		sqllist.add(sql2);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	public CoreReturnObject UPDATETAIL(MilSawResultDeatilEntity milsawdeatail) {
		ArrayList<String> sqllist = new ArrayList<String>();
		String sql = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.UpdatesawdeatailTail");
		String sql1 = CoreXmlSqlParsersFactory
				.getSql("PipSawFast.UpdateSawRackTail");
		sql = EntityTool.fillSqlByEntity(sql, milsawdeatail);
		sql1 = EntityTool.fillSqlByEntity(sql1, milsawdeatail);
		sqllist.add(sql);
		sqllist.add(sql1);
		String sql2 = "UPDATE MIL_SAW_RESULT T SET T.CUT_TRAIL_LENGTH= "
				+ milsawdeatail.getCutTrailLength()
				+ " WHERE T.JUDGE_STOVE_NO='";
		sql2 += milsawdeatail.getJudgeStoveNo()
				+ "' AND NVL(PRO_PLAN_ID,'/') = NVL('"
				+ milsawdeatail.getProPlanId()
				+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('"
				+ milsawdeatail.getGxPlanNo() + "','/')";
		sqllist.add(sql2);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	/***
	 * 分切结束（改标志\生成冷区进程表管号队列）
	 * 
	 * @param milsawdeatail
	 * @return
	 */
	public CoreReturnObject UPDATESawFlag(MilSawResultDeatilEntity milsawdeatail) {

		String sql = " UPDATE MIL_SAW_RESULT_DEATIL t SET t.SAW_FLAG='3' WHERE t.JUDGE_STOVE_NO ='";
		sql += milsawdeatail.getJudgeStoveNo()
				+ "' AND NVL(PRO_PLAN_ID,'/') = NVL('"
				+ milsawdeatail.getProPlanId()
				+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('"
				+ milsawdeatail.getGxPlanNo() + "','/')";
		return this.setData(sql);
	}

	/***
	 * 分切结束写进程表长度\重量
	 * 
	 * @param milsawdeatail
	 * @return
	 */
	public CoreReturnObject updatePortOrderLength(
			MilSawResultDeatilEntity milsawdeatail) {
		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 contraThick = "";// 控制壁厚
		String outDiameter = "";// 目标外径
		String producName = "";// 名称
		String proWgt = "";// 重量
		try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String updatePortSql = CoreXmlSqlParsersFactory
					.getSql("updatePortOrderLength.updateLen");// 修改进程表长度重量
			PreparedStatement updatePortPsta = connection
					.prepareStatement(updatePortSql);
			/*sql = CoreXmlSqlParsersFactory
					.getSql("updatePortOrderLength.selectPipSize");*/
			sql = CoreXmlSqlParsersFactory
			.getSql("updatePortOrderLength.selectPipSize2");
			psta = connection.prepareStatement(sql);
			psta.setString(1, milsawdeatail.getJudgeStoveNo());
			psta.setString(2, milsawdeatail.getProPlanId());
			psta.setString(3, milsawdeatail.getGxPlanNo());
			rs = psta.executeQuery();
			if (rs.next()) {
				/*contraThick = rs.getString("CONTROL_THICKP");
				outDiameter = rs.getString("OUTDIAMETER");
				producName = rs.getString("VARIETIES");*/
				
				contraThick = rs.getString("AIMWALLTHICK"); //控制壁厚
				outDiameter = rs.getString("AIMOUTDIAMETER"); //控制外径
			}
			rs.close();
			psta.close();
			
			if(contraThick == null || contraThick == ""){
				sql = CoreXmlSqlParsersFactory
				.getSql("updatePortOrderLength.selectPipSize");
				psta = connection.prepareStatement(sql);
				psta.setString(1, milsawdeatail.getJudgeStoveNo());
				psta.setString(2, milsawdeatail.getProPlanId());
				psta.setString(3, milsawdeatail.getGxPlanNo());
				rs = psta.executeQuery();
				if (rs.next()) {
					contraThick = rs.getString("CONTROL_THICKP");//控制壁厚
					outDiameter = rs.getString("OUTDIAMETER");//控制外径
				}
				rs.close();
				psta.close();
			}
			/*if (producName.contains("油管") || producName.contains("套管")
					|| producName.contains("接箍")) {
				if (producName.contains("管线")) {
					proWgt = new DecimalFormat("0.000").format(0.02466
							* Double.parseDouble(contraThick)
							* (Double.parseDouble(outDiameter) * 1.003 - Double
									.parseDouble(contraThick)));
				} else {
					proWgt = new DecimalFormat("0.000").format(0.02466
							* Double.parseDouble(contraThick)
							* (Double.parseDouble(outDiameter) * 1.006 - Double
									.parseDouble(contraThick)));
				}

			} else {*/
				proWgt = new DecimalFormat("0.000").format(0.02466
						* Double.parseDouble(contraThick)
						* (Double.parseDouble(outDiameter) - Double
								.parseDouble(contraThick)));
			//}

			updatePortPsta.setString(1, milsawdeatail.getOrderLength());
			updatePortPsta.setString(2, proWgt);
			updatePortPsta.setString(3, milsawdeatail.getOrderLength());
			updatePortPsta.setString(4, proWgt);
			updatePortPsta.setString(5, milsawdeatail.getOrderLength());
			updatePortPsta.setString(6, milsawdeatail.getJudgeStoveNo());
			updatePortPsta.setString(7, milsawdeatail.getProPlanId());
			updatePortPsta.setString(8, milsawdeatail.getGxPlanNo());
			updatePortPsta.addBatch();

			updatePortPsta.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;
	}

	public CoreReturnObject UpdteRackCOL_NONEW(String HeatNo, String colno,
			Integer RowNum, Integer SAW_NUM, Integer rowno) {
		CoreReturnObject cro = new CoreReturnObject();
		Connection conn = null;
		PreparedStatement psta = null;
		PreparedStatement psta1 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs3 = null;
		try {
			conn = this.getDao("TgDao").getConnection();
			conn.setAutoCommit(false);

			int rowcouts = 0;
			int seqmin = 1;
			int seqmax = 1;

			String sql = "";
			String sql1 = "";
			String sql2 = "";
			String sql3 = "";
			sql = "select Sum(t.row_count) as row_count from MIL_SAW_RESULT_DEATIL t WHERE t.judge_stove_no='";
			sql += HeatNo;
			sql += "' AND ROW_NO <= " + rowno;
			psta = conn.prepareStatement(sql);
			rs = psta.executeQuery();
			while (rs.next()) {
				rowcouts = rs.getInt(1);
			}
			seqmin = (rowcouts - RowNum) * SAW_NUM + 1;
			seqmax = rowcouts * SAW_NUM;

			sql1 = "update Mil_Testing_Rack t set t.COL_NO= '" + colno;
			sql1 += "'  where t.seq between " + seqmin;
			sql1 += "  and " + seqmax;
			sql1 += " and t.judge_stove_no= '" + HeatNo;
			sql1 += "'";
			this.setData(sql1);

			sql2 = "update MIL_STRAIGH_RACK t set t.COL_NO=' " + colno;
			sql2 += " ' where t.seq between " + seqmin;
			sql2 += "  and " + seqmax;
			sql2 += " and t.judge_stove_no= '" + HeatNo;
			sql2 += "'";
			this.setData(sql2);

			// sql3="UPDATE MIL_SAW_RACK t SET t.col_no=";
			// sql3+=colno;
			// sql3+=" WHERE t.SEQ  in ( select SEQ FROM ( select a.seq AS SEQ from MIL_SAW_RACK a  where a.status=0 ";
			// sql3+=" AND a.judge_stove_no='"+HeatNo+"'";
			// //sql3+=" AND a.ROW_NO is not null AND a.col_no is  null order  by a.seq ) WHERE rownum between 1 and ";
			// sql3+=" AND a.ROW_NO is not null AND a.col_no is  null  order  by a.seq ) WHERE rownum between 1 and ";
			// sql3+=RowNum;
			// sql3+=")";
			// this.setData(sql3);
			sql3 = "UPDATE MIL_SAW_RACK t SET t.col_no=";
			sql3 += colno;
			sql3 += " WHERE t.row_no= ";
			sql3 += rowno;
			sql3 += " and t.judge_stove_no= '" + HeatNo;
			sql3 += "'";
			this.setData(sql3);

			rs.close();
			psta.close();
			conn.commit();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		cro.setResult(0);
		return cro;

	}

	public CoreReturnObject UpdateColno(String HeatNo, String colno,
			Integer RowNum, Integer SAW_NUM, Integer rowno, String proPlanId,
			String gxPlanNo) {
		ArrayList<String> sqllist = new ArrayList<String>();
		int re = 0;
		CoreReturnObject cro = new CoreReturnObject();
		Connection conn = null;
		PreparedStatement psta = null;
		PreparedStatement psta1 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs3 = null;
		try {
			conn = this.getDao("TgDao").getConnection();
			conn.setAutoCommit(false);

			int rowcouts = 0;
			int seqmin = 1;
			int seqmax = 1;

			String sql = "";
			String sql1 = "";
			String sql2 = "";
			String sql3 = "";
			String sql4 = "";
			sql = "select Sum(t.row_count) as row_count from MIL_SAW_RESULT_DEATIL t WHERE t.judge_stove_no='";
			sql += HeatNo + "' AND NVL(PRO_PLAN_ID,'/') = NVL('" + proPlanId
					+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('" + gxPlanNo
					+ "','/')";
			sql += " AND ROW_NO <= " + rowno;
			psta = conn.prepareStatement(sql);
			rs = psta.executeQuery();
			while (rs.next()) {
				rowcouts = rs.getInt(1);
			}
			seqmin = (rowcouts - RowNum) * SAW_NUM + 1;
			seqmax = rowcouts * SAW_NUM;

			sql1 = "update Mil_Testing_Rack t set t.COL_NO= '" + colno;
			sql1 += "'  where t.seq between " + seqmin;
			sql1 += "  and " + seqmax;
			sql1 += " and t.judge_stove_no= '" + HeatNo
					+ "' AND NVL(PRO_PLAN_ID,'/') = NVL('" + proPlanId
					+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('" + gxPlanNo
					+ "','/')";
			// this.setData(sql1);
			sqllist.add(sql1);

			sql2 = "update MIL_STRAIGH_RACK t set t.COL_NO=' " + colno;
			sql2 += " ' where t.seq between " + seqmin;
			sql2 += "  and " + seqmax;
			sql2 += " and t.judge_stove_no= '" + HeatNo
					+ "' AND NVL(PRO_PLAN_ID,'/') = NVL('" + proPlanId
					+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('" + gxPlanNo
					+ "','/')";
			// this.setData(sql2);
			sqllist.add(sql2);

			// sql3="UPDATE MIL_SAW_RACK t SET t.col_no=";
			// sql3+=colno;
			// sql3+=" WHERE t.SEQ  in ( select SEQ FROM ( select a.seq AS SEQ from MIL_SAW_RACK a  where a.status=0 ";
			// sql3+=" AND a.judge_stove_no='"+HeatNo+"'";
			// //sql3+=" AND a.ROW_NO is not null AND a.col_no is  null order  by a.seq ) WHERE rownum between 1 and ";
			// sql3+=" AND a.ROW_NO is not null AND a.col_no is  null  order  by a.seq ) WHERE rownum between 1 and ";
			// sql3+=RowNum;
			// sql3+=")";
			// this.setData(sql3);
			sql3 = "UPDATE MIL_SAW_RACK t SET t.col_no=";
			sql3 += colno;
			sql3 += " WHERE t.row_no= ";
			sql3 += rowno;
			sql3 += " and t.judge_stove_no= '" + HeatNo
					+ "' AND NVL(PRO_PLAN_ID,'/') = NVL('" + proPlanId
					+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('" + gxPlanNo
					+ "','/')";
			// this.setData(sql3);
			sqllist.add(sql3);

			sql4 = " UPDATE  MIL_SAW_RESULT_DEATIL T SET T.COL_NO= '" + colno
					+ "' WHERE T.JUDGE_STOVE_NO= '" + HeatNo
					+ "' AND NVL(PRO_PLAN_ID,'/') = NVL('" + proPlanId
					+ "','/') AND NVL(GX_PLAN_NO,'/') = NVL('" + gxPlanNo
					+ "','/')" + " AND T.ROW_NO=" + rowno;
			sqllist.add(sql4);

			cro = this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
					sqllist);
			rs.close();
			psta.close();
			conn.commit();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		// cro.setResult(0);
		return cro;
	}

}
