package com.steering.mes.zgmil.report;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
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 FrmUpdateBalYearMonth extends DBCommon {

	/***
	 * 查询数据
	 * 
	 * @param balYearMonth
	 * @param judgeStoveNo
	 * @param pline
	 * @return
	 */
	public CoreReturnObject getBalYearMonth(String balYearMonth,
			String judgeStoveNo, String pline) {
		String sql = CoreXmlSqlParsersFactory
				.getSql("FrmUpdateBalYearMonth.getBalYearMonth");
		String sql1 = "";
		if (!balYearMonth.equals("")) {
			sql1 += " AND T.BAL_YEAR_MONTH = '" + balYearMonth + "'";
		}
		if (!judgeStoveNo.equals("")) {
			sql1 = " AND T.JUDGE_STOVE_NO LIKE '%" + judgeStoveNo.trim()
					+ "%' ";
		}
		if (!pline.equals("")) {
			sql1 += " AND T.PLINE_CODE = '" + pline + "' ";
		}
		sql = sql.replace("#something#", sql1);
		return this.getData(sql);
	}

	/***
	 * 调整结转年月
	 * @param parm
	 * @return
	 */
	public CoreReturnObject doUpdate(ArrayList<ArrayList<String>> parm) {
		CoreReturnObject cro = new CoreReturnObject();
		Connection connection = this.getDao(CoreConstantData.sysbeanstr)
				.getConnection();
		PreparedStatement psta = null;
        String sql1 = "";
        String is_carry_voer = "";  //是否结转(0 未结转 1结转)
        ResultSet rs = null;
		try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String sql = CoreXmlSqlParsersFactory
					.getSql("FrmUpdateBalYearMonth.doUpdate"); // 调整结转年月
			PreparedStatement updateBalYearMonth = connection
					.prepareStatement(sql);

			for (int i = 0; i < parm.size(); i++) {
				ArrayList<String> list = (ArrayList<String>) parm.get(i);
				//查询是否结转
				/*sql1 = CoreXmlSqlParsersFactory.getSql("FrmUpdateBalYearMonth.doQueryIsCarryVoer");
				psta = connection.prepareStatement(sql);
				psta.setString(1, list.get(0));
				psta.setString(2, list.get(1));
				psta.setString(3, list.get(6));
				rs = psta.executeQuery();
				if(rs.next())
				{
					is_carry_voer = rs.getString("is_carry_voer");
				}
				
				rs.close();
				psta.close();
				if(is_carry_voer.equals("1"))
				{
					cro.setV_errMsg("已结转的数据不允许调整结转年月！");
					return cro;
				}*/
				
				updateBalYearMonth.setString(1, list.get(0));
				updateBalYearMonth.setString(2, list.get(1));
				updateBalYearMonth.setInt(3, Integer.parseInt(list.get(2)));
				updateBalYearMonth.setString(4, list.get(3));
				updateBalYearMonth.addBatch();
				updateBalYearMonth.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 balYearMonth
	 * @param judgeStoveNo
	 * @param
	 * @return
	 */
	public CoreReturnObject getApplyBalYearMonth(String balYearMonth,
			String judgeStoveNo, String[] plineCodes) {
		String sql = CoreXmlSqlParsersFactory
				.getSql("FrmUpdateBalYearMonth.getApplyBalYearMonth");
		
		if (!balYearMonth.equals("")) {
			sql += " AND T.BAL_YEAR_MONTH = '" + balYearMonth + "'";
		}
		if (!judgeStoveNo.equals("")) {
			sql += " AND T.JUDGE_STOVE_NO = '"+judgeStoveNo+"'";
		}
		sql += CoreConverter.toSqlConditionString("T.PLINE_CODE", plineCodes);
		sql += " AND T.TARGET IN('B','C')";
		return this.getData(sql);
	}
	
	/**
	 * 查询判定信息
	 * @param balYearMonth
	 * @param judgeStoveNo
	 * @param gx
	 * @return
	 */
	public CoreReturnObject getApplyBalYearMonthGX(String balYearMonth,
			String judgeStoveNo, String gx, String[] pline) {
		String sql = CoreXmlSqlParsersFactory
				.getSql("FrmUpdateBalYearMonth.getApplyBalYearMonth");
		
		if (!balYearMonth.equals("")) {
			sql += " AND T.BAL_YEAR_MONTH = '" + balYearMonth + "'";
		}
		if (!judgeStoveNo.equals("")) {
			sql += " AND T.JUDGE_STOVE_NO = '"+judgeStoveNo+"'";
		}
		if (!gx.equals("")) {
			sql += " AND T.PROCESS_CODE = '" + gx + "' ";
		}

		sql += CoreConverter.toSqlConditionString("T.PLINE_CODE",pline);
		
		return this.getData(sql);
	}
	
	/***
	 * 查询判定信息
	 * @param balYearMonth
	 * @param judgeStoveNo
	 * @param
	 * @return
	 */
	public CoreReturnObject getApplyBalYearMonthBelong(String balYearMonth,
			String judgeStoveNo, String[] arr) {
		String sql = CoreXmlSqlParsersFactory
				.getSql("FrmUpdateBalYearMonth.getApplyBalYearMonth");
		sql += CoreConverter.toSqlConditionString("T.BELONG_CODE_JUGDE", arr);
		if (!balYearMonth.equals("")) {
			sql += " AND T.BAL_YEAR_MONTH = '" + balYearMonth + "'";
		}
		if (!judgeStoveNo.equals("")) {
			sql += " AND T.JUDGE_STOVE_NO = '"+judgeStoveNo+"'";
		}
		sql += " AND T.TARGET = 'A'";
		return this.getData(sql);
	}
	
	/***
	 * 调整判定表结转年月
	 * @param parm
	 * @return
	 */
	public CoreReturnObject doUpdateYearMonth(ArrayList<ArrayList<String>> parm) {
		CoreReturnObject cro = new CoreReturnObject();
		Connection connection = this.getDao(CoreConstantData.sysbeanstr)
				.getConnection();
		PreparedStatement psta = null;
        String sql1 = "";
        String is_carry_voer = "";  //是否结转(0 未结转 1结转)
        String INSTOCK_FLAG = "";  //缴库标志(1是、0否)
        int bal_year_month = 0 ;//期初表最大结转年月
        int bal_year_month1 = 0 ;//期初表最小结转年月
        int bal_year_month2 = 0 ;//判定单的结转年月
        int bal_year_month3 = 0 ;//产权出库的最小结转年月
        ResultSet rs = null;
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM");
		try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String sql = CoreXmlSqlParsersFactory
					.getSql("FrmUpdateBalYearMonth.doUpdateYearMonth"); // 调整判定表结转年月
			PreparedStatement updateBalYearMonth = connection
					.prepareStatement(sql);
			for (int i = 0; i < parm.size(); i++) {
				ArrayList<String> list = (ArrayList<String>) parm.get(i);
				//查询是否结转，,是否缴库
				sql1 = CoreXmlSqlParsersFactory.getSql("FrmUpdateBalYearMonth.doQueryIsCarryVoer");
				psta = connection.prepareStatement(sql1);
				psta.setString(1, list.get(1));
				psta.setString(2, list.get(2));
				psta.setString(3, list.get(8));
				rs = psta.executeQuery();
				if(rs.next())
				{
					is_carry_voer = rs.getString("is_carry_voer");
					INSTOCK_FLAG = rs.getString("INSTOCK_FLAG");
				}
				
				rs.close();
				psta.close();
				if(is_carry_voer.equals("1"))
				{
					cro.setV_errMsg("已结转的数据不允许调整结转年月！");
					return cro;
				}
				if(list.get(7).equals("A") && INSTOCK_FLAG.equals("1")){
					cro.setV_errMsg("成品已缴库，不允许调整结转年月！");
					return cro;
				}
				//查询期初表最大结转年月
				sql1 = CoreXmlSqlParsersFactory.getSql("FrmUpdateBalYearMonth.doQueryBalYearMonth");
				psta = connection.prepareStatement(sql1);
				rs = psta.executeQuery();
				if(rs.next())
				{
					bal_year_month = Integer.parseInt(rs.getString("bal_year_month"));
					bal_year_month1 = Integer.parseInt(rs.getString("bal_year_month1"));
				}
				
				rs.close();
				psta.close();
				if(Integer.parseInt(list.get(0)) < bal_year_month1)
				{
					cro.setV_errMsg("您选择的结转年月不被允许调整，最小调整到【"  + bal_year_month1 + "】。");
					return cro;
				}
				//根据判定单号查询判定表结转年月
				sql1 = CoreXmlSqlParsersFactory.getSql("FrmUpdateBalYearMonth.doQueryAPPLY");
				psta = connection.prepareStatement(sql1);
				psta.setString(1, list.get(1));
				rs = psta.executeQuery();
				if(rs.next())
				{
					bal_year_month2 = Integer.parseInt(rs.getString("bal_year_month"));
				}
				
				rs.close();
				psta.close();
				if(bal_year_month2 < bal_year_month1)
				{
					cro.setV_errMsg("结转年月小于【"+bal_year_month1+"】的不允许调整结转年月！");
					return cro;
				}
				
				//产权出库的最小结转年月
				sql1 = CoreXmlSqlParsersFactory.getSql("FrmUpdateBalYearMonth.doQueryBalYearMonth3");
				psta = connection.prepareStatement(sql1);
				psta.setString(1, list.get(2));
				psta.setString(2, list.get(3));
				psta.setString(3, list.get(4));
				psta.setString(4, list.get(8));
				rs = psta.executeQuery();
				if(rs.next())
				{
					
					bal_year_month3 = Integer.parseInt(rs.getString("bal_year_month"));
				}
				rs.close();
				psta.close();
				if(bal_year_month3 > 0){
					if(Integer.parseInt(list.get(0)) > bal_year_month3 && !list.get(7).equals("A") )
					{
						cro.setV_errMsg("您选择的结转年月不被允许调整，该炉批已产权出库，最大调整到【"  + bal_year_month3 + "】。");
						return cro;
					}
				}
				
				
				
				
				updateBalYearMonth.setString(1, list.get(0));
				updateBalYearMonth.setString(2, list.get(1));
				updateBalYearMonth.setString(3, list.get(2));
				updateBalYearMonth.setString(4, list.get(3));
				updateBalYearMonth.setString(5, list.get(4));
				updateBalYearMonth.setString(6, list.get(5));
				updateBalYearMonth.setString(7, list.get(6));
				updateBalYearMonth.setString(8, list.get(7));
				updateBalYearMonth.setString(9, list.get(8));
				updateBalYearMonth.setString(10,list.get(9)+"-25 12:00:00");
				updateBalYearMonth.setString(11,list.get(9)+"-25 12:00:00");
				updateBalYearMonth.addBatch();
				updateBalYearMonth.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 doUpdateBelongName(ArrayList<ArrayList<String>> parm) {
		CoreReturnObject cro = new CoreReturnObject();
		Connection connection = this.getDao(CoreConstantData.sysbeanstr)
				.getConnection();
		PreparedStatement psta = null;
        String sql1 = "";
        String is_carry_voer = "";  //是否结转(0 未结转 1结转)
        ResultSet rs = null;
		try {
			connection.setAutoCommit(false); // 取消自动提交事务
			String sql = CoreXmlSqlParsersFactory
					.getSql("FrmUpdateBalYearMonth.doUpdateBelongName"); // 调整所属权
			PreparedStatement updateBalYearMonth = connection
					.prepareStatement(sql);
			String BELONG_CODE_JUGDE = "";
			for (int i = 0; i < parm.size(); i++) {
				ArrayList<String> list = (ArrayList<String>) parm.get(i);
				
				//查询是否结转
				sql1 = CoreXmlSqlParsersFactory.getSql("FrmUpdateBalYearMonth.doQueryIsCarryVoer");
				psta = connection.prepareStatement(sql1);
				psta.setString(1, list.get(1));
				psta.setString(2, list.get(2));
				psta.setString(3, list.get(7));
				rs = psta.executeQuery();
				if(rs.next())
				{
					is_carry_voer = rs.getString("is_carry_voer");
				}
				
				rs.close();
				psta.close();
				if(is_carry_voer.equals("1"))
				{
					cro.setV_errMsg("已结转的数据不允许调整所属权！");
					return cro;
				}
				
				if(list.get(0).equals("生产计划部")){
					BELONG_CODE_JUGDE = "002001001";
				}else if(list.get(0).equals("168机组")){
					BELONG_CODE_JUGDE = "002001017003";
				}else if(list.get(0).equals("250机组")){
					BELONG_CODE_JUGDE = "002001016003";
				}else if(list.get(0).equals("258机组")){
					BELONG_CODE_JUGDE = "002001016002";
				}else if(list.get(0).equals("460机组")){
					BELONG_CODE_JUGDE = "002001018003";
				}else if(list.get(0).equals("产品制造部")){
					BELONG_CODE_JUGDE = "002023005";
				}else if(list.get(0).equals("天淮508机组")){
					BELONG_CODE_JUGDE = "002023018001";
				}
				
				updateBalYearMonth.setString(1, BELONG_CODE_JUGDE);
				updateBalYearMonth.setString(2, list.get(0));
				updateBalYearMonth.setString(3, list.get(1));
				updateBalYearMonth.setString(4, list.get(2));
				updateBalYearMonth.setString(5, list.get(3));
				updateBalYearMonth.setString(6, list.get(4));
				updateBalYearMonth.setString(7, list.get(5));
				updateBalYearMonth.setString(8, list.get(6));
				updateBalYearMonth.addBatch();
				updateBalYearMonth.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;
	}
}
