package com.steering.mes.zgmil.coup;

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

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.MilPlanEntity;
import com.steering.mes.zgmil.entity.MilFeedPlanEntity;

public class GetSQLDate extends DBCommon {

	/**
	 * 查询简单上料计划
	 * 
	 * @param FeedRowPlan
	 *            上料计划实体类
	 * @return CoreReturnObject
	 */
	public CoreReturnObject getPlanQuery() {
		CoreReturnObject cro = new CoreReturnObject();
		Connection connSQL = null;
		Connection connORACLE = null;
		PreparedStatement psta = null;
		PreparedStatement psta1 = null;
		PreparedStatement psta2 = null;
		PreparedStatement psta3 = null;
		PreparedStatement DelFeedPlanPsta = null;
		PreparedStatement DelMilPlanPsta = null;
		String result = "无";
		ResultSet rs = null;
		ResultSet rs1 = null;

		try {
//			TZH 24.4.24 注销物流接口
//			connSQL = this.getDao("MSSQL1Dao").getConnection();
			connORACLE = this.getDao("TgDao").getConnection();
//			TZH 24.4.24 注销物流接口
//			connSQL.setAutoCommit(false);
			connORACLE.setAutoCommit(false);
			String DelFeedPlan = "delete from mil_feed_plan t where t.saw_falg = '0'";
			String DelMilPlan = "delete from mil_plan t where t.status = '00'";
			DelFeedPlanPsta = connORACLE.prepareStatement(DelFeedPlan);
			DelFeedPlanPsta.executeUpdate();
			DelMilPlanPsta = connORACLE.prepareStatement(DelMilPlan);
			DelMilPlanPsta.executeUpdate();
			DelFeedPlanPsta.close();
			DelMilPlanPsta.close();
			// connORACLE.commit();
//			TZH 24.4.24 注销物流接口
//			String getFeedPlan = CoreXmlSqlParsersFactory
//					.getSql("GetSQLDate.FeedPlanQuery");
//			psta = connSQL.prepareStatement(getFeedPlan);
			rs = psta.executeQuery();
			if (!rs.next()) {
				result = "未查询到可同步数据";
				throw new SQLException("上料支数未空");
			} else {

				int check_sql = 0;
				while (rs.next()) {
					// if(check_sql == 52)
					// {
					// check_sql = 67;
					// }
					String ID = rs.getString(1);
					String ZPH = rs.getString(2);// 轧批号
					String ZGLcode = rs.getString(3);// 产出炉号
					String PCDSL = rs.getString(4);// 坯长度上料
					String DBC = rs.getString(5);// 单倍尺长度
					String BC = rs.getString(6);// 坯子倍尺
					String YCSF = rs.getString(7);// 余长轧否轧切
					String MEMO = rs.getString(8);// 备注
					String PSLzhi = rs.getString(9);// 管坯上料支
					if (PSLzhi == null) {
						// result=ZGLcode;
						// result+="上料支数未空";
						// cro.setV_errMsg(String.valueOf(result).trim());
						// return cro;
						// PSLzhi = "0";
						throw new SQLException("上料支数未空");
					}
					String PDBzhi = rs.getString(10);// 管坯上料单倍尺支
					String PSLdun = rs.getString(11);// 管坯上料吨
					String DC = rs.getString(12);// 定尺长度
					String QY = rs.getString(13);// 切余长度
					String QYCL = rs.getString(14);// 切余处理
					if (QYCL == null) {
						QYCL = "";
					}
					String QYZ = rs.getString(15);// 切余重
					String SYCD = rs.getString(16);// 使用长度
					String SLQYZ = rs.getString(17);// 上料切余重
					String DM = rs.getString(18);// 断面
					String CX = rs.getString(19);
					if (CX.equals("250机组")) {
						CX = "C008";
					} else if (CX.equals("168机组")) {
						CX = "C010";
					} else if (CX.equals("460机组")) {
						CX = "C017";
					} else if (CX.equals("258机组")) {
						CX = "C009";
					} else if (CX.equals("ASSEL机组")) {
						CX = "C012";
					}
					if (SYCD == null) {
						SYCD = "";
					}
					Float DZZ = Float.valueOf(Integer.valueOf((int) (Float
							.valueOf(PSLdun)
							/ Integer.valueOf(PSLzhi) * 1000))) / 1000;
					String DZZ_TXT = DZZ.toString();
					Float QYZC;
					String QYZC_TXT = "";
					if (QY == null || QY.equals("") || QY == "0") {
						QYZC_TXT = "0";
					} else {
						QYZC_TXT = QY;
						QYZC = Float.valueOf(QYZC_TXT) ;
						QYZC_TXT = QYZC.toString();// 切余重
					}

					Float DBZ;
					String DBZ_txt;
					String QYZ_TXT;
					Float QYZ_zhong;
					if (QYZ == null || QYZ.equals("")) {
						QYZ_TXT = "0";
					} else {
						QYZ_zhong = Float.valueOf(QYZ) ;
						QYZ_TXT = QYZ_zhong.toString();// 切余重
					}

					DBZ = Float.valueOf(Integer.valueOf((int) ((Float
							.valueOf(PSLdun) - Float.valueOf(QYZ_TXT))
							/ Float.valueOf(PDBzhi) * 1000))) / 1000;// 单倍重
					DBZ_txt = DBZ.toString();
					check_sql = check_sql + 1;
					System.out.println(check_sql);

					String sql = "";
					sql += " INSERT INTO MIL_FEED_PLAN(FEED_ROW_NO,LOT_NO,JUDGE_STOVE_NO,DB_NUM,MAT_HEIGHT,MAT_TOTAL_HEIGHT,FEED_NUM,FEED_LENGTH,USE_LENGTH";
					sql += ",CUT_TARGET,CUT_LENGTH,CUT_WEIGHT,DB_LENGTH,NON_USE_LENGTH,PIPE_SECTION,DB_HEIGHT,FEED_NUM_PLAN,PLINE_CODE)";
					sql += "values (";
					sql += "'" + ID + "','" + ZPH + "','" + ZGLcode + "-01"
							+ "','" + BC + "','" + DZZ_TXT + "','" + PSLdun
							+ "','" + PSLzhi + "','" + PCDSL + "',nvl('" + SYCD
							+ "',0),nvl('";
					sql += QYCL + "',''),'" + QYZC_TXT + "','" + QYZ_TXT
							+ "','" + DC + "','" + QYZC_TXT + "','" + DM
							+ "','" + DBZ_txt + "','" + PSLzhi + "','" + CX
							+ "')";
					System.out.println(sql);

					psta1 = connORACLE.prepareStatement(sql);

					int count = psta1.executeUpdate();
					psta1.close();
				}

//				TZH 24.4.24 注销物流接口
//				String getPlan = CoreXmlSqlParsersFactory
//						.getSql("GetSQLDate.PlanQuery");
//				psta2 = connSQL.prepareStatement(getPlan);
				rs1 = psta2.executeQuery();

				while (rs1.next()) {
					String ID = rs1.getString(1);
					String CXID = rs1.getString(2);// 产线ID
					String ZPH = rs1.getString(3);// 轧批号
					String ZGLcode = rs1.getString(4);// 产出炉号
					String GZ = rs1.getString(5);// 钢种
					String PDM = rs1.getString(6);// 坯断面
					String PSTANDEAR = rs1.getString(7);// 坯标准
					String ZZCD = rs1.getString(8);// 轧制长度
					String FQ = rs1.getString(9);// 分切
					String MBCD = rs1.getString(10);// 目标长度
					String CPZHI = rs1.getString(11);// 成品计划支
					if (CPZHI.equals("NULL")) {
						CPZHI = "1";
					}
					String CPDUN = rs1.getString(12);// 成品计划吨ee
					String GYZT = rs1.getString(13);// 工艺状态
					String CX = rs1.getString(14);// 产线h
					if (CX.equals("250机组")) {
						CX = "C008";
					} else if (CX.equals("168机组")) {
						CX = "C010";
					} else if (CX.equals("460机组")) {
						CX = "C017";
					} else if (CX.equals("258机组")) {
						CX = "C009";
					} else if (CX.equals("ASSEL机组")) {
						CX = "C012";
					}
					String HTH = rs1.getString(15);// 合同号
					String GYH = rs1.getString(16);// 工艺号
					String MEMO = rs1.getString(17);// 备注
					String SLzhi = rs1.getString(18);// 管坯上料支
					String SLDBZ = rs1.getString(19);// 管坯上料单倍尺支
					String SLD = rs1.getString(20);// 管坯上料吨
					String DCCD = rs1.getString(21);// 定尺长度
					String QYCL = rs1.getString(22);// 切余处理
					String QYZ = rs1.getString(23);// 切余重
					String SLQY = rs1.getString(24);// 上料切余重
					String KIND = rs1.getString(25);// KIND
					String STEELLEVEL = rs1.getString(26);// STEELLEVEL
					String WAIJING = rs1.getString(27);// WAIJING
					String BIHOU = rs1.getString(28);// BIHOU
					String ZGSTANDARD = rs1.getString(29);// ZGSTANDARD
					String LGSTANDARD = rs1.getString(30);// LGSTANDARD
					String CUSTOMER = rs1.getString(31);// CUSTOMER
					String TARGET = rs1.getString(32);// TARGET
					String LENGTHMIN = rs1.getString(33);// LENGTHMIN
					String LENGTHMAX = rs1.getString(34);// LENGTHMAX
					String changedate = rs1.getString(35);// CUSTOMER
					String[] changedate_sz = changedate.split("[.]");
					changedate = changedate_sz[0].toString();
					// changedate = changedate.substring(0,
					// changedate.length()-3);
					String cxstu = rs1.getString(36);
					String QYCD = rs1.getString(37);// TARGET
					String QT = rs1.getString(38);
					String QW = rs1.getString(39);// TARGET
					String CPSTANDARD = rs1.getString(40);
					if (QYCD == null || QYCD.equals("")) {
						QYCD = "0";
					}
					Float DBZ;
					String DBZ_txt;
					if (QYZ == null || QYZ.equals("")) {
						QYZ = "0";
					}
					DBZ = Float.valueOf(Integer.valueOf((int) ((Float
							.valueOf(SLD) - Float.valueOf(QYZ))
							/ Float.valueOf(SLDBZ) * 1000))) / 1000;// 单倍重
					DBZ_txt = DBZ.toString();
					String sql = "";
					sql += " INSERT INTO mil_plan(ID,SEQ,JUDGE_STOVE_NO,LOT_NO,CONTRACT_NO,IMP_PROCESS,PLINE_CODE,PROCESS_STATUS,DB_LENGTH";
					sql += ",DB_HEIGHT,ROLL_LENGTH,SAW_NUM,TARGET_LENGTH,FINISH_USER,TARGET_LIABRARY,VARIETIES,PIPE_SIZE,BUCKLE_TYPE,LENGTH_RANGE,";
					sql += "ROLL_STANDRAD,PIPE_SECTION,STEELNAME,GRADENAME,STEEL_STANDRAD,TARGET,BRANCH,TON,CUT_TARGET,CUT_LENGTH,CUT_WEIGHT,DB_NUM,START_TIME,STATUS,NODE_STATUS,CUT_HEAD_LENGTHP,CUT_TRAIL_LENGTHP,PRODUCE_STANDRAD)";
					sql += "values (";
					sql += "'" + ID + "','" + CXID + "','" + ZGLcode + "-01"
							+ "','" + ZPH + "','" + HTH + "','" + GYH + "','"
							+ CX + "','" + GYZT + "','" + DCCD + "','";
					sql += DBZ_txt + "','" + ZZCD + "','" + FQ + "','" + MBCD
							+ "','" + CUSTOMER + "','" + TARGET + "','" + KIND
							+ "','" + WAIJING + "*" + BIHOU + "','','"
							+ LENGTHMIN + "-" + LENGTHMAX + "','";
					sql += ZGSTANDARD + "','" + PDM + "','" + STEELLEVEL
							+ "','" + GZ + "','" + LGSTANDARD + "','" + TARGET
							+ "','" + CPZHI + "','" + CPDUN + "','" + QYCL
							+ "','" + QYCD + "','" + QYZ + "','" + SLDBZ
							+ "',to_date('" + changedate
							+ "','yyyy-mm-dd hh24:mi:ss'),'00','00','" + QT
							+ "','" + QW + "','" + CPSTANDARD + "')";
					System.out.println(sql);
					psta3 = connORACLE.prepareStatement(sql);
					int count = psta3.executeUpdate();

				}
				// }
				// String sql_updatestatus =
				// "update [TPCO_CXPLAN].[dbo].[tabcxslsj] set cxstu = '10' where cxstu = '00'";
				// Statement sta = connSQL.createStatement();
				//
				// sta.executeUpdate(sql_updatestatus);
				//			
				// sta.close();
				psta3.close();
				psta2.close();
				rs.close();
				rs1.close();
			}

			connORACLE.commit();
//			TZH 24.4.24 注销物流接口
//			connSQL.commit();

			connORACLE.close();
//			TZH 24.4.24 注销物流接口
//			connSQL.close();
		} catch (SQLException sqle) {

			sqle.printStackTrace();
			try {
//				TZH 24.4.24 注销物流接口
//				connSQL.rollback();
				connORACLE.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} finally {
			try {
				// cro.setV_errMsg(String.valueOf(result).trim());
//				TZH 24.4.24 注销物流接口
//				if (connSQL != null && !connSQL.isClosed()) {
//					connSQL.close();
//				}
				if (connORACLE != null && !connORACLE.isClosed()) {
					connORACLE.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		cro.setResult(0);
		return cro;

	}
	
	public CoreReturnObject CheckPlan(MilPlanEntity milplan)
	{
		String sql="";
		sql+="select * from MIL_PLAN t where t.judge_stove_no='";
		sql+=milplan.getJudgeStoveNo();
		sql+="'";
		sql+="  AND  T.ID= '";
		sql+=milplan.getId();
		sql+="'";
		return this.getData(sql);
	}
	
	public CoreReturnObject DelOraclePlan(String pline)
	{
		ArrayList<String> sqllist = new ArrayList<String>();
		String DelFeedPlan = "delete from mil_feed_plan t where t.saw_falg = '0' AND t.pline_code='";
		DelFeedPlan+=pline;
		DelFeedPlan+="'";
		String DelMilPlan = "delete from mil_plan t where t.status = '00' AND t.pline_code='";
		DelMilPlan+=pline;
		DelMilPlan+="'";
		sqllist.add(DelFeedPlan);
		sqllist.add(DelMilPlan);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}
	
//	TZH 24.4.24 注销物流接口
//	public CoreReturnObject PlanQuery1(String flag,String PlineCode) {
//		String sql = "";
//		sql = CoreXmlSqlParsersFactory.getSql("GetSQLDate.PlanQueryNEW");
//		sql+=" AND 产线='"+PlineCode+"'";
//		sql+=" order by start_time";
//		return this.getDao("MSSQL1Dao").ExcuteSortResultByQuery(sql);
//	}

	public CoreReturnObject PlanQuery2(String flag,String PlineCode) {
		String sql = "";
		sql = CoreXmlSqlParsersFactory.getSql("GetSQLDate.PlanQueryNEWHas");
		sql+=" AND PLINE_CODE='"+PlineCode+"'";
		sql+=" order by start_time";
		return this.getData(sql);
	}
	
//	TZH 24.4.24 注销物流接口
//	public CoreReturnObject FeedQuery1(String flag,String PlineCode) {
//		String sql = "";
//		sql = CoreXmlSqlParsersFactory.getSql("GetSQLDate.FeedPlanQueryNEW");
//		sql+=" AND 产线='"+PlineCode+"'";
//		return this.getDao("MSSQL1Dao").ExcuteSortResultByQuery(sql);
//	}

	public CoreReturnObject FeedQuery2(String flag,String PlineCode) {
		String sql = "";
		sql = CoreXmlSqlParsersFactory.getSql("GetSQLDate.FeedPlanQueryNEWHas");
		sql+=" AND PLINE_CODE='"+PlineCode+"'";
		return this.getData(sql);
	}

	public CoreReturnObject SysncPlan(MilPlanEntity milplan, ArrayList arr) {
		ArrayList<String> sqllist = new ArrayList<String>();

		String sql = "";
		sql = CoreXmlSqlParsersFactory.getSql("GetSQLDate.AddPlan");
		if(milplan.getTarget().equals("A"))
		{
			sql = sql.replace("#PRT_FLAG#", "1");
		}else
		{
			sql = sql.replace("#PRT_FLAG#", "0");
		}
		milplan.setGxPlanNo(milplan.getGxPlanNo().trim());
		sql = EntityTool.fillSqlByEntity(sql, milplan);
		sqllist.add(sql);

		for (int i = 0; i < arr.size(); i++) {
			String sqlfeed = "";
			MilFeedPlanEntity feed = new MilFeedPlanEntity();
			feed = (MilFeedPlanEntity) arr.get(i);
			sqlfeed = CoreXmlSqlParsersFactory.getSql("GetSQLDate.AddFeed");
			feed.setGxPlanNo(feed.getGxPlanNo().trim());
			sqlfeed = EntityTool.fillSqlByEntity(sqlfeed, feed);
			sqllist.add(sqlfeed);
		}

		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}
	
//	TZH 24.4.24 注销物流接口
//	public CoreReturnObject AfterSysncPlan(MilPlanEntity milplan, ArrayList arr)
//	{
//		ArrayList<String> sqllist=new ArrayList<String>();
//		
//		for(int i=0;i< arr.size();i++)
//		{
//			String sql="";
//			MilFeedPlanEntity feed = new MilFeedPlanEntity();
//			feed = (MilFeedPlanEntity) arr.get(i);
//			sql = CoreXmlSqlParsersFactory.getSql("GetSQLDate.AfterSysncPlan");
//			sql = EntityTool.fillSqlByEntity(sql, feed);
//			sqllist.add(sql);
//		}
//		return this.getDao("MSSQL1Dao").ExcuteTransction(sqllist);
//	}
	
	public CoreReturnObject AfterSysncPlanBack(MilPlanEntity milplan, ArrayList arr)
	{
		ArrayList<String> sqllist=new ArrayList<String>();
		String sql1="";String sql2="";
		sql1 = CoreXmlSqlParsersFactory.getSql("GetSQLDate.BackPlan");
		sql2 = CoreXmlSqlParsersFactory.getSql("GetSQLDate.BackFeed");
		sql1 = EntityTool.fillSqlByEntity(sql1, milplan);
		sql2 = EntityTool.fillSqlByEntity(sql2, milplan);
		sqllist.add(sql1);
		sqllist.add(sql2);
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}

	public CoreReturnObject SysncPlanBack(ArrayList arr) {
		ArrayList<String> sqllist = new ArrayList<String>();
		for (int i = 0; i < arr.size(); i++) {
			String sql1 = "";
			String sql2 = "";
			MilPlanEntity plan = new MilPlanEntity();
			plan = (MilPlanEntity) arr.get(i);
			sql1 = CoreXmlSqlParsersFactory.getSql("GetSQLDate.BackPlan");
			sql2 = CoreXmlSqlParsersFactory.getSql("GetSQLDate.BackFeed");
			sql1 = EntityTool.fillSqlByEntity(sql1, plan);
			sql2 = EntityTool.fillSqlByEntity(sql2, plan);
			sqllist.add(sql1);
			sqllist.add(sql2);
		}
		return this.getDao(CoreConstantData.sysbeanstr).ExcuteTransction(
				sqllist);
	}
	
	/**
	 * 查询计划状态
	 * @param plan
	 * @return
	 */
	public CoreReturnObject SelectPlanStatus(MilPlanEntity plan) {
			String sql = "";
			//MilPlanEntity plan = new MilPlanEntity();
			sql = CoreXmlSqlParsersFactory.getSql("GetSQLDate.SelectPlanStatus");
			sql = EntityTool.fillSqlByEntity(sql, plan);
		return this.getData(sql);
	}
	
//	TZH 24.4.24 注销物流接口
//	public CoreReturnObject AfterSysncPlanBack(ArrayList arr)
//	{
//		ArrayList<String> sqllist = new ArrayList<String>();
//		for (int i = 0; i < arr.size(); i++)
//		{
//			String sql="";
//			MilPlanEntity plan = new MilPlanEntity();
//			plan = (MilPlanEntity) arr.get(i);
//			sql = CoreXmlSqlParsersFactory.getSql("GetSQLDate.AfterSysncPlanBack");
//			sql = EntityTool.fillSqlByEntity(sql, plan);
//			sqllist.add(sql);
//		}
//		return this.getDao("MSSQL1Dao").ExcuteTransction(
//				sqllist);
//	}

}
