package com.steering.mes.signature;

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

import com.steering.comm.db.DBCommon;
import com.steering.common.EntityTool;
import com.steering.mes.zgmil.entity.OfflineProcess;
import com.steering.mes.zgmil.entity.OfflineProcessAuditEntity;

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

public class FrmOfflineProcessAudit extends DBCommon{
	
	public CoreReturnObject QueryOfflineProcessAudit(String StartTime, String EndTime,
			String isAudit) {
		String sql = "";
		String strwhere = "";
		 if(!StartTime.equals("") && !EndTime.equals(""))
		 {
			 strwhere += " AND to_char(t1.AUDIT_TIME1,'yyyy-mm-dd') between '"+StartTime+"' and '"+EndTime+"'";
		 }
		 if(!isAudit.equals(""))
		 {
			 strwhere += "AND t1.AUDIT_MAN1 LIKE '% "+isAudit+"%'";
		 }
		 sql +="select t.result_no  result_no, t1.id,t.JUDGE_STOVE_NO 判定炉号,t.STOVE_NO 熔炼炉号,t.FLAG_STOVE_NO 标识炉号,t.BATCH_NO 批号,t.TARGET 产品标识,t.REASON1  一级原因,t.REASON2   二级原因,t.REASON3   三级原因,t.PROCESS_DESC 生产线, ";
		 sql +="to_char(t.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss') 生产日期, t.STEELNAME 钢级,t.PRODUCNAME 品名,t.STD_STYLE_DESC 标准类别,t.DISPOSAL_OPINION 处置意见,";	 
		 sql += "case when t.IS_AUDIT = '0' then '未发起审核' when t.IS_AUDIT = '1' then '已发起审核' when t.IS_AUDIT = '2' then '审核通过' end 审核状态, ";
		 sql +="to_char(t1.AUDIT_TIME1, 'yyyy-mm-dd hh24:mi:ss') 发起时间,t1.AUDIT_MAN1 发起人,t1.AUDIT_MAN2 审批人,t1.AUDIT_DEP1 发起部门,t1.AUDIT_MAN2 申请人,to_char(t1.AUDIT_TIME2, 'yyyy-mm-dd hh24:mi:ss') 申请时间," ;
		 sql +="t1.MEMO2 审批意见,t1.AUDIT_DEP2 申请部门,case when t1.AUDIT_STATE2 = '0' then '等待审批' when t1.AUDIT_STATE2 = '1' then '开始审批' when t1.AUDIT_STATE2 = '2' then '审批通过' when t1.AUDIT_STATE2 = '3' then '审批失败' end 审批状态   ";
		 sql += " from OFFLINE_PROCESS t, OFFLINE_PROCESS_AUDIT t1 where t.result_no = t1.result_no "+strwhere+" order by t1.AUDIT_TIME1 desc" ;
	     return this.getData(sql);      
	}
	
	public CoreReturnObject QueryOfflineAudit(String id)
	{
		if (id.equals("")||null ==id ){
			return null;
		}
		String sql = "SELECT t1.ID ,t1.AUDIT_MAN2 审批人,to_char(t1.AUDIT_TIME2, 'yyyy-mm-dd hh24:mi:ss') 审批时间, t1.MEMO2 审批意见,t1.AUDIT_DEP2 审批部门,case when t1.AUDIT_STATE2 = '0' then '等待审批' when t1.AUDIT_STATE2 = '1' then '开始审批' when t1.AUDIT_STATE2 = '2' then '审批通过' when t1.AUDIT_STATE2 = '3' then '审批失败' end 审批状态   FROM"
				   + "  OFFLINE_PROCESS_AUDIT_S t1, OFFLINE_PROCESS_AUDIT t2  where t1.id = t2.id and  t1.id = '"+id+"'";
		return this.getData(sql); 
	}
	
	
	@SuppressWarnings("unchecked")
	public CoreReturnObject doAddAudits(ArrayList<String> pram) {
		
		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 addsql = CoreXmlSqlParsersFactory
					.getSql("FrmOfflineProcessAudit.doAddAudits");
			PreparedStatement pstaaddsql = connection.prepareStatement(addsql);
			for (String json : pram) {
				OfflineProcessAuditEntity entity = EntityTool.getEntityByJSONStr(json, OfflineProcessAuditEntity.class);
				String id = entity.getId();
				String memo2  = entity.getMemo2();
				String state = entity.getAuditState2();
				String userName = entity.getAuditMan2();
				String depatment = entity.getAuditDep2();
				String resultNo = entity.getResultNo();
				//String id  = entity.getId();
				sql = CoreXmlSqlParsersFactory
						.getSql("FrmOfflineProcessAudit.selectAuditsPass"); // 查询是否已经审批通过
				psta = connection.prepareStatement(sql);
				psta.setString(1, id);
				rs = psta.executeQuery();
				if (rs.next()) {
					acount = Integer.parseInt(rs.getString("acount"));
				}
				if (acount>0) {
					cro.setV_errMsg("存在已经审批通过的申请");
					rs.close();
					psta.close();
					return cro;
				}
				pstaaddsql.setString(1, userName);
                pstaaddsql.setString(2, state);
				pstaaddsql.setString(3, depatment);
				pstaaddsql.setString(4, memo2);
				pstaaddsql.setString(5, id);
				pstaaddsql.addBatch();
				pstaaddsql.executeBatch();
				
				if(depatment.equals("质保部")){
					String sql1 = "update OFFLINE_PROCESS set IS_AUDIT = 2 where RESULT_NO = '"+resultNo+"' ";
					this.setData(sql1);
				}
			}
			cro.setV_errMsg("操作成功");
			connection.commit();
		}
		catch (Exception e) {
			cro.setV_errMsg(e.getMessage());
			cro.setV_errCode(-1);
			if (connection != null) {
				try {
					connection.rollback(); // 回滚事务
				} catch (Exception e2) {
				}
			}
		} finally {
			try {
				if (connection != null && !connection.isClosed()) {
					connection.close();
				}
			} catch (Exception e2) {
			}
		}
		return cro;
				
	}
	   public CoreReturnObject doAudit(ArrayList<String> parm) {
		   
	       return this.setData(sqlSplit("FrmOfflineProcessAudit.doUpdate", parm, "doUpdate"), null);
	   }
	   
	
	 private ArrayList<String> sqlSplit(String sqlID, ArrayList<String> parm, String add) {
	       ArrayList<String> sqlList = new ArrayList<String>();// 存放执行语句
	       for (String json : parm) {
	    	   OfflineProcessAuditEntity Load = EntityTool.getEntityByJSONStr(json, OfflineProcessAuditEntity.class);
	           String sql = CoreXmlSqlParsersFactory.getSql(sqlID);
	           sql = EntityTool.fillSqlByEntity(sql, Load);
	           sqlList.add(sql);
	       }
	       return sqlList;
	   }

}
