package QCM.QCM01.QCM0106;

import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;
import QCM.COMMUNAL.communalClass;
import QCM.COMMUNAL.QCM02.SqlJoint;

import java.text.ParseException;


public class QueryDefect extends CoreIComponent  {

	CoreReturnObject cro = new CoreReturnObject();	
	SqlSession SqlSession = 
			QCM.COMMUNAL.SqlSessionBuilder.openSqlSession();
	QCM.COMMUNAL.OrderSqMapper mapper = SqlSession.getMapper(QCM.COMMUNAL.OrderSqMapper.class);
	
	public String StrSky(String begintime,String endtime){
		String sql = "select (to_date('"+endtime+"','yyyy-MM-dd') - to_date('"+begintime+"','yyyy-MM-dd')) SKY from dual";
		HashMap list = mapper.queryOne(sql);
		Double ble = Double.parseDouble(list.get("SKY").toString());
		String tstr="FALSE";
		if(ble>=0 && ble <=30 ){
			tstr="TRUE";
		}
		return tstr;
	}
	//轧钢历史操作记录
	public CoreReturnObject Query(String begintime,String endtime,String prodline,String bbatchno,String ebatchno){
		
		if (!SqlJoint.IsNullOrSpace(begintime) && !SqlJoint.IsNullOrSpace(endtime)) {	
			String sky=StrSky(begintime,endtime);
			if(sky.equals("FALSE")){
				SqlSession.close();
				cro.setV_errCode(-1);
				cro.setV_errMsg("查询时间不能大于30天,请核实查询时间条件!");
				return cro;
			}
		}
		String swhere = " 1 = 1";	
		if (!SqlJoint.IsNullOrSpace(begintime) && !SqlJoint.IsNullOrSpace(endtime)) {				//操作时间
			swhere += " and to_date(to_char(OPERATE_TIME,'yyyy-mm-dd'),'yyyy-mm-dd') between to_date('"+begintime+"','yyyy-mm-dd') and to_date('"+endtime+"','yyyy-mm-dd')";
		}		
		if (!SqlJoint.IsNullOrSpace(bbatchno)) {	//起始轧批号
			swhere += "and batchno >= '"+bbatchno+"'";
		}
		if (!SqlJoint.IsNullOrSpace(ebatchno)) {    //最后轧批号
			swhere += "and batchno <= '"+ebatchno+"'";
		}
		if (!SqlJoint.IsNullOrSpace(prodline)) {   //Psc_desc
			swhere += " and  prod_line = '"+prodline+"'";
		}
		String sqlucomm = "select t.OPERATE_TYPE OPERATE_TYPE,t.PARAMS PARAMS,t.OPERATE_NAME OPERATE_NAME,"
				+ "t.OPERATE_TIME OPERATE_TIME,decode(t.PROD_LINE,\r\n" + 
				"              'LT1',\r\n" + 
				"              '连退线',\r\n" + 
				"              'RZ1',\r\n" + 
				"              '热轧线',\r\n" + 
				"              'SZ1',\r\n" + 
				"              '酸轧线',\r\n" + 
				"              'GX1',\r\n" + 
				"              '高棒线',\r\n" + 
				"              'GX2',\r\n" + 
				"              '高线',\r\n" + 
				"              'BC2',\r\n" + 
				"              '棒二线',\r\n" + 
				"              'ZB1',\r\n" + 
				"              '中板线',\r\n" + 
				"              'HB1',\r\n" + 
				"              '厚板线') PROD_LINE,t.OPERATION_INTERFACE OPERATION_INTERFACE,t.BATCHNO BATCHNO,t.MEMO MEMO,"
				+ "				decode(T.JUDGE_RESULT,'0','待判','1','合格','2','不合格') JUDGE_RESULT "
				+ " from qcm_judge_operate_log t where "+swhere;
	
			cro=this.getDao("test1Dao").ExcuteQuery(sqlucomm.toString());			
		return cro;		
	}
	//炼钢历史操作记录
		public CoreReturnObject QueryLG(String begintime,String endtime,String prodline,String bbatchno){
			if (!SqlJoint.IsNullOrSpace(begintime) && !SqlJoint.IsNullOrSpace(endtime)) {	
				String sky=StrSky(begintime,endtime);
				if(sky.equals("FALSE")){
					SqlSession.close();
					cro.setV_errCode(-1);
					cro.setV_errMsg("查询时间不能大于30天,请核实查询时间条件!");
					return cro;
				}
			}
			String swhere = " 1 = 1";	
			if (!SqlJoint.IsNullOrSpace(begintime) && !SqlJoint.IsNullOrSpace(endtime)) {				//操作时间
				swhere += " and to_date(to_char(OPERATE_TIME,'yyyy-mm-dd'),'yyyy-mm-dd') between to_date('"+begintime+"','yyyy-mm-dd') and to_date('"+endtime+"','yyyy-mm-dd')";
			}		
			if (!SqlJoint.IsNullOrSpace(bbatchno)) {	//炉号/产品序号
				swhere += "and PARAMS like '"+bbatchno+"%'";
			}			
			if (!SqlJoint.IsNullOrSpace(prodline)) {   //产线
				swhere += " and  prod_line = '"+prodline+"'";
			}
			String sqlucomm = "select t.OPERATE_TYPE OPERATE_TYPE,t.PARAMS PARAMS,t.OPERATE_NAME OPERATE_NAME,"
					+ "t.OPERATE_TIME OPERATE_TIME,decode(t.PROD_LINE,\r\n" + 
					"              '4001LGX',\r\n" + 
					"              '二炼钢',\r\n" + 
					"              '4001LG0',\r\n" + 
					"              '一炼钢',''\r\n" + 
					") PROD_LINE,t.OPERATION_INTERFACE OPERATION_INTERFACE,t.BATCHNO BATCHNO,t.MEMO MEMO,"
					+ "				decode(T.JUDGE_RESULT,'0','待判','1','合格','2','不合格') JUDGE_RESULT "
					+ " from qcm_judge_operate_log t where "+swhere;		
				cro=this.getDao("test1Dao").ExcuteQuery(sqlucomm.toString());
			return cro;
		}	
		//轧钢历史操作记录
		public CoreReturnObject QueryJHY(String begintime,String endtime,String prodline,String bbatchno,String ebatchno){
			String swhere = " 1 = 1";	
			if (!SqlJoint.IsNullOrSpace(begintime) && !SqlJoint.IsNullOrSpace(endtime)) {				//操作时间
				swhere += " and to_date(to_char(OPERATE_TIME,'yyyy-mm-dd'),'yyyy-mm-dd') between to_date('"+begintime+"','yyyy-mm-dd') and to_date('"+endtime+"','yyyy-mm-dd')";
			}		
			if (!SqlJoint.IsNullOrSpace(bbatchno)) {	//起始轧批号
				swhere += "and batchno >= '"+bbatchno+"'";
			}
			if (!SqlJoint.IsNullOrSpace(ebatchno)) {    //最后轧批号
				swhere += "and batchno <= '"+ebatchno+"'";
			}
			if (!SqlJoint.IsNullOrSpace(prodline)) {   //Psc_desc
				swhere += " and  prod_line = '"+prodline+"'";
			}
			String sqlucomm = "select t.OPERATE_TYPE OPERATE_TYPE,t.PARAMS PARAMS,t.OPERATE_NAME OPERATE_NAME,"
					+ "t.OPERATE_TIME OPERATE_TIME,decode(t.PROD_LINE,\r\n" + 
					"              'LT1',\r\n" + 
					"              '连退线',\r\n" + 
					"              'RZ1',\r\n" + 
					"              '热轧线',\r\n" + 
					"              'SZ1',\r\n" + 
					"              '酸轧线',\r\n" + 
					"              'GX1',\r\n" + 
					"              '高棒线',\r\n" + 
					"              'GX2',\r\n" + 
					"              '高线',\r\n" + 
					"              'BC2',\r\n" + 
					"              '棒二线',\r\n" + 
					"              'ZB1',\r\n" + 
					"              '中板线',\r\n" + 
					"              'HB1',\r\n" + 
					"              '厚板线') PROD_LINE,t.OPERATION_INTERFACE OPERATION_INTERFACE,t.BATCHNO BATCHNO,t.MEMO MEMO,"
					+ "				decode(T.JUDGE_RESULT,'0','待判','1','合格','2','不合格') JUDGE_RESULT "
					+ " from qcm_judge_operate_log t where "+swhere;
		
				cro=this.getDao("test1Dao").ExcuteQuery(sqlucomm.toString());			
			return cro;		
		}	
		public CoreReturnObject QueryMemo(String prodline){
			String swhere = " 1 = 1";	
			
			if (!SqlJoint.IsNullOrSpace(prodline)) {   //Psc_desc
				swhere += " and  pline_code = '"+prodline+"'";
			}
			String sqlucomm = "select MEMO,memo_code,creat_name,to_char(creat_time,'yyyy/mm/dd HH24:MI:SS') creat_time,decode(pline_code," + 
					"	'JB1','卷板'," + 
					"	'ZHB', '中厚板'," + 
					"	'XB1','线棒'," + 					
					"	'LG','炼钢')pline_code,mod_id,to_char(mod_time,'yyyy/mm/dd HH24:MI:SS') mod_time from deal_memo where "+swhere;
		
				cro=this.getDao("test1Dao").ExcuteQuery(sqlucomm.toString());			
			return cro;		
		}
		public CoreReturnObject AddMemo(String prodline,String memo,String usename,String memo_code) {
			String swhere = " 1 = 1";	
			
			if (!SqlJoint.IsNullOrSpace(prodline)) {   //Psc_desc
				swhere += " and  pline_code = '"+prodline+"'";
			}
			/*if (!SqlJoint.IsNullOrSpace(memo)) {   //Psc_desc
				swhere += " and  memo = '"+memo+"'";
			}*/
			if (!SqlJoint.IsNullOrSpace(memo_code)) {   //Psc_desc
				swhere += " and  memo_code = '"+memo_code+"'";
			}
			try{
				String sql = "select * from deal_memo where "+swhere;
			List<HashMap> list1 = mapper.query(sql);
			if(list1.size() >0){
				throw new  Exception("该产线已有该处理备注代码，请换一个处理备注代码后再添加");
			}
			String sql1 = "insert into deal_memo(pline_code,memo,memo_code,creat_name,creat_time) "
					+ " values('"+prodline+"','"+memo+"','"+memo_code+"','"+usename+"',"
							+ "TO_DATE('"+communalClass.getDate()+"','YYYY-MM-DD HH24:MI:SS'))";
			mapper.insert(sql1.toString());
			}catch(Exception e){
				SqlSession.rollback();
				SqlSession.close();
				cro.setV_errCode(-1);
				cro.setV_errMsg("处理备注失败"+e.getMessage());
				return cro;
			}
			SqlSession.commit();
			SqlSession.close();
			cro.setV_errCode(1);
			return cro;
		}
		
		public CoreReturnObject EMemo(String prodline,String memo,String username,String memo_code) {
			String swhere = " 1 = 1";	
			
			if (!SqlJoint.IsNullOrSpace(prodline)) {   //Psc_desc
				swhere += " and  pline_code = '"+prodline+"'";
			}
			if (!SqlJoint.IsNullOrSpace(memo)) {   //Psc_desc
				swhere += " and  memo_code = '"+memo_code+"'";
			}
			try{
			String sql = "select * from deal_memo where "+swhere;
			List<HashMap> list1 = mapper.query(sql);
			if(list1.size() <=0){
				throw new  Exception("该产线没有该备注，请点击添加备注");
			}
			String sql1 = "update deal_memo set memo = '"+memo+"',mod_id = '"+username+"',"
					+ " mod_time = TO_DATE('"+communalClass.getDate()+"','YYYY-MM-DD HH24:MI:SS') "
							+ " where memo_code = '"+memo_code+"'";
			mapper.insert(sql1.toString());
			}catch(Exception e){
				SqlSession.rollback();
				SqlSession.close();
				cro.setV_errCode(-1);
				cro.setV_errMsg("修改处理备注失败"+e.getMessage());
				return cro;
			}
			SqlSession.commit();
			SqlSession.close();
			cro.setV_errCode(1);
			return cro;
		}
}