XinYu_LZMES/.svn/pristine/2f/2f4aee4cd1f6122bb5deba23a05511bf2312f59a.svn-base

package QCM.JHY01.JHY0103;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.session.SqlSession;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;

import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;



/**
 * 查询第三方认证申报信息
 * @author freedom
 *
 */
public class QuerryThirdInfo 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 <=183 ){//半年182.5
			tstr="TRUE";
		}
		return tstr;
	}
	/**
	 * 查询发起的第三方申报记录(按轧批)
	 * @param strDate
	 * @param endDate
	 * @param orgName
	 * @param batch
	 * @param design_key
	 * @return
	 */
	public CoreReturnObject QueryBatchNo(String strDate,String endDate,String orgName,String batch,String shordh) {
		try {
			if (StringUtils.isNotBlank(strDate)&& StringUtils.isNotBlank(endDate)) {	
				String sky=StrSky(strDate,endDate);
				if(sky.equals("FALSE")){
					SqlSession.close();
					cro.setV_errCode(-1);
					cro.setV_errMsg("查询时间不能大于半年,请核实查询时间条件!");
					return cro;
				}
			}
			String sql = "select max(m.Cert_No) 认证申报编号,\r\n" + 
		  		"       max(m.Cert_Inst_Code) 认证机构代码,\r\n" + 
		  		"       max(m.Cert_Inst_Name) 认证机构,\r\n" + 
		  		"       max(m.Cert_Status) 确认状态,\r\n" + 
		  		"       max(m.Apply_Org_No) 申请机构编码,\r\n" + 
		  		"       max(m.Apply_Org_Desc) 申请机构,\r\n" + 
		  		"       max(m.Dep_Type_Code) 部门级别,\r\n" + 
		  		"       max(m.Memo) 备注,\r\n" + 
		  		"       max(m.Create_Name) 申报人,\r\n" + 
		  		"       max(to_char(m.Create_Time, 'yyyy-MM-dd hh24:mi:ss')) 申报时间,\r\n" + 
		  		"       max(m.Send_Flag) 发送状态,\r\n" + 
		  		"       count(t.Material_No) Material_No,\r\n" + 
		  		"       max(t.DESIGN_KEY) DESIGN_KEY,      \r\n" + 
		  		"       max(t.PSC) PSC,\r\n" + 
		  		"       max(t.PSC_DESC) PSC_DESC,\r\n" + 
		  		"       max(t.PROD_CODE)PROD_CODE,\r\n" + 
		  		"       max(t.PROD_NAME)PROD_NAME,\r\n" + 
		  		"       max(t.STEEL_CODE)STEEL_CODE,\r\n" + 
		  		"       max(t.STEEL_NAME)STEEL_NAME,\r\n" + 
		  		"       max(t.STD_CODE)STD_CODE,\r\n" + 
		  		"       max(t.STD_NAME)STD_NAME,\r\n" + 
		  		"       max(t.DELIVERY_STATE_CODE)DELIVERY_STATE_CODE,\r\n" + 
		  		"       max(t.DELIVERY_STATE_DESC)DELIVERY_STATE_DESC,\r\n" + 
		  		"       max(t.DELIVERY_DATE)DELIVERY_DATE,\r\n" + 
		  		"       max(t.CUT_TYPE)CUT_TYPE,\r\n" + 
		  		"       max(t.HEAT_NO)HEAT_NO,\r\n" + 
		  		"       t.BATCH_NO,\r\n" + 
		  		"       max(t.INSPECTION_LOT)INSPECTION_LOT,\r\n" + 
		  		"       max(t.BOARD_NO)BOARD_NO,\r\n" + 
		  		"       max(t.THICK)THICK,\r\n" + 
		  		"       max(t.WIDTH)WIDTH,\r\n" + 
		  		"       max(t.LENGTH)LENGTH,\r\n" + 
		  		"       sum(nvl(t.mat_weight2,t.MAT_WEIGHT))MAT_WEIGHT,\r\n" + 
		  		"       max(t.PLINE_CODE)PLINE_CODE,\r\n" + 
		  		"       max(t.VESSEL)VESSEL,\r\n" + 
		  		"       max(t.PLINE_NAME)PLINE_NAME,\r\n" + 
		  		"       max(APPROACH)APPROACH,\r\n" + 
		  		"       max(INCEPTCORPCODE)INCEPTCORPCODE,\r\n" + 
		  		"       max(BUYERCODE)BUYERCODE,\r\n" + 
		  		"       max(CONTRACT_NO)CONTRACT_NO,\r\n" + 
		  		"       max(decode((SELECT count(1)\r\n" + 
		  		"                 from zj_result_all@xgcx z\r\n" + 
		  		"               where z.JUDGERESULT = '1' and z.BILLETID = T.Material_No), \r\n" + 
		  		"              '0',\r\n" + 
		  		"              '否',\r\n" + 
		  		"              '是')) PHYFSTSRESULT1,\r\n" + 
		  		//"        max((select Z.fst_judgeresult\r\n" + 
		  		//"          from ZJ_RESULT_ALL@XGCX z\r\n" + 
		  		//"         where Z.BILLETID = T.MATERIAL_NO)) PHYFSTSRESULT\r\n" + 
		  		"   '1' PHYFSTSRESULT \r\n" + 
		  		"  from QCM_MAT_CERT_D t\r\n" + 
		  		"  left join QCM_MAT_CERT_M m\r\n" + 
		  		"    on t.cert_no = m.cert_no\r\n" + 		  				  		
		  		"where 1=1 and t.VALIDFLAG is null ";
		  if(!"".equals(orgName)){
				sql += " and m.CERT_INST_NAME like '"+orgName+"%' ";
			}
			
			if(!"".equals(batch)){
				sql +=" and t.BATCH_NO like '"+batch+"%' ";
			}

			if(!"".equals(shordh)){
				sql +=" and t.DESIGN_KEY like '"+shordh+"%' ";
			}
			 
			if(!"".equals(strDate)){
				sql +=" and m.CREATE_TIME >= to_date('"+strDate+" 00:00:01','yyyy-mm-dd,hh24:mi:ss')" ;
			}
			if(!"".equals(endDate)){
				sql +=" and m.CREATE_TIME <= to_date('"+endDate+" 23:59:59','yyyy-mm-dd,hh24:mi:ss')";
			}
			sql += " group by  t.BATCH_NO ";
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm.append(sql);
			
				List<HashMap> listEle= mapper.query(sqlucomm.toString());
				cro.setResult(listEle);
				//cro.setResult(jsonArray); 
			} catch(Exception ex){
				cro.setV_errCode(-1);
				cro.setV_errMsg("获取数据出错"+ex.getMessage());
				SqlSession.rollback();
			}
			SqlSession.close();
		    return cro;
	}
	
	
	/**
	 * 查询发起的第三方申报记录
	 * @param strDate
	 * @param endDate
	 * @param orgName
	 * @param batch
	 * @param design_key
	 * @return
	 */
	public CoreReturnObject QueryMainInfo(String strDate,String endDate,String orgName,String batch,String shordh) {
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm.append("select CERT_NO,CERT_INST_CODE,CERT_INST_NAME,CERT_STATUS,APPLY_ORG_NO,");
			sqlucomm.append("APPLY_ORG_DESC,DEP_TYPE_CODE,DUTY_ID,INSTANCE_ID,MEMO,CREATE_ID,CREATE_NAME,");
			sqlucomm.append(" SEND_FLAG,");
			sqlucomm.append("to_char(CREATE_TIME,'yyyy-MM-dd hh24:mi:ss') CREATE_TIME,UPDATE_ID,UPDATE_NAME,to_char(UPDATE_TIME,'yyyy-MM-dd hh24:mi:ss')UPDATE_TIME FROM QCM_MAT_CERT_M WHERE  1=1");
			if(!"".equals(orgName)){
				sqlucomm.append(" and CERT_INST_NAME like '%"+orgName+"%' ");
			}
			if(!"".equals(strDate)){
				sqlucomm.append(" and CREATE_TIME >= to_date('"+strDate+" 00:00:01','yyyy-mm-dd,hh24:mi:ss')");
			}
			if(!"".equals(endDate)){
				sqlucomm.append(" and CREATE_TIME <= to_date('"+endDate+" 23:59:59','yyyy-mm-dd,hh24:mi:ss')");
			}
			
			
			try {
				ResultSet results=this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString());
				JSONArray jsonArray=resultSetToJsonArry(results);
				cro.setResult(jsonArray); 
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    return cro;
	}
	
	
	
	//申报记录事件 查询发起的第三方申报子数据
	public CoreReturnObject QueryDetailInfo(String cert_no,String batch,String design_key) {
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm.append("select CERT_NO,MATERIAL_NO,DESIGN_KEY,PSC,PROD_CODE,");
			sqlucomm.append("PROD_NAME,STEEL_CODE,STEEL_NAME,STD_CODE,STD_NAME,to_char(to_date(DELIVERY_DATE,'yyyy-MM-dd hh24:mi:ss'),'yyyy-MM-dd') DELIVERY_DATE,CUT_TYPE,");
			sqlucomm.append("HEAT_NO,BATCH_NO,INSPECTION_LOT,BOARD_NO,THICK,WIDTH,LENGTH,MAT_WEIGHT,PLINE_CODE,PLINE_NAME FROM QCM_MAT_CERT_D WHERE  1=1 and VALIDFLAG is null ");
			if(!"".equals(cert_no)){
				sqlucomm.append(" and CERT_NO like '"+cert_no+"%' ");
			}
			if(!"".equals(batch)){
				sqlucomm.append(" and BATCH_NO like '"+batch+"%' ");
			}
			if(!"".equals(design_key)){
				sqlucomm.append(" and DESIGN_KEY like '"+design_key+"%' ");
			}
			
			try {
				ResultSet results=this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString());
				JSONArray jsonArray=resultSetToJsonArry(results);
				cro.setResult(jsonArray); 
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    return cro;
	}
	
	//申报记录 合并
	public CoreReturnObject QueryMainInfoHb(String strDate,String endDate,String cert_inst_name,String BATCH_NO,String design_key,String prod_namemome,String pline_name,String certNo) {
		try {
			if (StringUtils.isNotBlank(strDate)&& StringUtils.isNotBlank(endDate)) {	
				String sky=StrSky(strDate,endDate);
				if(sky.equals("FALSE")){
					SqlSession.close();
					cro.setV_errCode(-1);
					cro.setV_errMsg("查询时间不能大于半年,请核实查询时间条件!");
					return cro;
				}
			}
		String sql="select m.Cert_No 认证申报编号, "
				   +"    m.Cert_Inst_Code 认证机构代码, "
				  // +"    m.Cert_Inst_Name 认证机构, "
				   +" dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) from qcm_ord_design_std_pic p where p.design_key = t.design_key)) 认证机构,"
				   +"    m.Cert_Status 确认状态, "
				   +"    m.Apply_Org_No 申请机构编码, "
				   +"    m.Apply_Org_Desc 申请机构, "
				   +"    m.Dep_Type_Code 部门级别, "
				  +" case t.PLINE_CODE "
				  +"       when '4001ZB1' then "
				  +"        decode((nvl((select ORDERNO "
				  +"                      from kcz_turnofflist@xgcx z "
				  +"                     where z.billetid = t.Material_No), "
				  +"                    'null')), "
				  +"               'null', "
				  +"               '已脱单', "
				  +"                decode((select count(1) from kcz_turnofflist@xgcx z,sel_pactdetail_new@xgcx s where z.orderno=s.ORDERNO and z.billetid = t.Material_No ), "
                  +"               '0','已换老单' ,m.Memo)) "
				  +"       when '4001HB1' then "
				  +"        decode((nvl((select ALLOTORDERFORM "
				  +"                      from kcH_turnofflist@xgcx h "
				  +"                     where h.billetid = t.Material_No), "
				  +"                    'null')), "
				  +"               'null', "
				  +"               '已脱单', "
				  +"               decode((select count(1) from kcH_turnofflist@xgcx h,sel_pactdetail_new@xgcx s where h.ALLOTORDERFORM=s.ORDERNO and h.billetid = t.Material_No ), "
                  +"               '0','已换老单' ,m.Memo)) "
				  +"     end 备注, "
				   +"    m.Create_Name 申报人, "
				   +"    to_char(m.Create_Time,'yyyy-MM-dd hh24:mi:ss') 申报时间, "
				   +"    m.Send_Flag 发送状态, "
				   +"    t.Material_No, "
				   +"    t.DESIGN_KEY, "
				   +"    t.PSC, "
				   +"    t.PSC_DESC, "
				   +"    t.PROD_CODE, "
				   +"    t.PROD_NAME, " 
				   +"    t.STEEL_CODE, "
				   +"    t.STEEL_NAME, "
				   +"    t.STD_CODE, "
				   +"    t.STD_NAME, "
				   +"    t.DELIVERY_STATE_CODE, "
				   +"    t.DELIVERY_STATE_DESC, "
				   +"    t.DELIVERY_DATE, "
				   +"    t.CUT_TYPE, "
				   +"    t.HEAT_NO, "
				   +"    t.BATCH_NO, "
				   +"    t.INSPECTION_LOT, "
				   +"    t.BOARD_NO, "
				   +"    t.THICK, "
				   +"    t.WIDTH, "
				   +"    t.LENGTH, "
				   +"    nvl(t.mat_weight2,to_char(t.MAT_WEIGHT, 'fm990.099')) MAT_WEIGHT, "
				   +"    t.PLINE_CODE, "
				   +"    t.VESSEL, "
				   +"    t.PLINE_NAME,APPROACH,INCEPTCORPCODE,BUYERCODE,CONTRACT_NO, "
				   
				   //+"  case when substr(t.DESIGN_KEY,0,2) = '19' then '非计划'  else decode((SELECT count(1) from zj_result_all@xgcx z "
			        // +"       where z.JUDGERESULT = '1' and z.BILLETID = T.Material_No) ,'0','待判','船检合格') end PHYFSTSRESULT1, "
			         
				+"	case t.PLINE_CODE "
				+"    when '4001ZB1' then "
			    +"           decode((nvl(substr((select ORDERNO from kcz_turnofflist@xgcx z where z.billetid = t.Material_No), 0, 2),'null')),'19','非计划', "
			    +"                     decode((SELECT count(1) from zj_result_all@xgcx z where z.JUDGERESULT = '1' and z.BILLETID = T.Material_No), "
			    +"                        '0', '待判','船检合格') ) "
			    +"    when '4001HB1' then "
			    +"          decode((nvl(substr((select ALLOTORDERFORM from kcH_turnofflist@xgcx h where h.billetid = T.Material_No), 0, 2),'null')),'19','非计划', "
			    +"                     decode((SELECT count(1) from zj_result_all@xgcx z where z.JUDGERESULT = '1' and z.BILLETID = T.Material_No), "
			    +"                        '0', '待判','船检合格') )  "
			    +"  end PHYFSTSRESULT1, "
				    
				    +" (select Z.fst_judgeresult  from ZJ_RESULT_ALL@XGCX z where Z.BILLETID = T.MATERIAL_NO and rownum=1) PHYFSTSRESULT,"
				  // +" '1' PHYFSTSRESULT, "
				   +" (select to_char(Z.fst_judge_time,'yyyy-MM-dd hh24:mi:ss')  from ZJ_RESULT_ALL@XGCX z where Z.BILLETID = T.MATERIAL_NO) 初检时间, "
				   + " t.VALIDFLAG,t.JUDGERESULT_NAME,t.JUDGERESULT_MEMO "
				 +" from QCM_MAT_CERT_D t "
				 +" left join QCM_MAT_CERT_M m "
				 +"   on t.cert_no = m.cert_no "
				 + "  where 1=1 and (t.VALIDFLAG is null or t.VALIDFLAG = '1' ) ";	
		
	
			if(!"".equals(cert_inst_name)){
				sql += " and m.CERT_INST_NAME like '%"+cert_inst_name+"%' ";
			}
			
			if(!"".equals(BATCH_NO)){
				sql +=" and t.BATCH_NO like '"+BATCH_NO+"%' ";
			}

			if(!"".equals(design_key)){
				sql +=" and t.DESIGN_KEY like '"+design_key+"%' ";
			}

			if(!"".equals(prod_namemome)){
				sql +=" and t.PSC_DESC like '"+prod_namemome+"%' ";
			}
			
			if(!"".equals(pline_name)){
				sql +=" and t.PLINE_NAME like '"+pline_name+"%' ";
			}
			
			if(!"".equals(certNo)){
				sql +=" and t.CERT_NO = '"+certNo+"' ";
			}
			 
			if(!"".equals(strDate)){
				sql +=" and m.CREATE_TIME >= to_date('"+strDate+" 00:00:01','yyyy-mm-dd,hh24:mi:ss')" ;
			}
			if(!"".equals(endDate)){
				sql +=" and m.CREATE_TIME <= to_date('"+endDate+" 23:59:59','yyyy-mm-dd,hh24:mi:ss')";
			}
			sql +="  order by m.CREATE_TIME desc";
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm.append(sql);
			
				List<HashMap> listEle= mapper.query(sqlucomm.toString());
				cro.setResult(listEle);
				}catch(Exception ex){
					cro.setV_errCode(-1);
					cro.setV_errMsg("获取数据出错"+ex.getMessage());
					SqlSession.rollback();
				}
				SqlSession.close();
		    return cro;
	}
	
	/**
	 * 查询库存数据厚板线
	 * @param strDate
	 * @param endDate
	 * @param orgName
	 * @param batch
	 * @param design_key
	 * @return
	 */

	public CoreReturnObject QueryKSInfo_N(String starttime,String endtime,String batch_no,String org_name,String design_key,String prod_namemome,String radio,String radioYN) {
		String sql2="";
		if (StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)) {	
			String sky=StrSky(starttime,endtime);
			if(sky.equals("FALSE")){
				SqlSession.close();
				cro.setV_errCode(-1);
				cro.setV_errMsg("查询时间不能大于半年,请核实查询时间条件!");
				return cro;
			}
		}
		if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
			sql2 +=" and t.creattime >= to_date('"+starttime+" 00:00:00', 'yyyy-MM-dd hh24:mi:ss')";
			sql2 +=" and t.creattime <= to_date('"+endtime+" 23:59:59', 'yyyy-MM-dd hh24:mi:ss') ";
		}
		
		if(!"".equals(org_name)){
			sql2 +=" and P.CERT_INST_NAME = '"+org_name+"' ";
		}
		
		String sql ="select * from (SELECT distinct T.BILLETID MATERIAL_NO, "
					+"   '' VESSEL, "
				    +"   P.DELIVERY_STATE_CODE, "
				    +"  P.DELIVERY_STATE_DESC, "
				    +"  TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
				    +"  C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
				    +"  T.STOVENO HEAT_NO, "
				    +"  T.ROLLNUMBER BATCH_NO, "
				    +"   Z.BILLETID_JY INSPECTION_LOT, "
				    +"   T.MOTHERBOARDNUMBER BOARD_NO, "
				    +"   T.PLY THICK, "
				    +"   T.WIDTH, "
				    +"   T.LENGTH, "
				    +"   T.THEORYWEIGHT MAT_WEIGHT, "
				    +"   '4001HB1' PLINE_CODE, "
				    +"   '厚板线' PLINE_NAME, "
				    +"   P.CERT_INST_CODE CERT_INST_CODE, "
				    //+" P.CERT_INST_NAME CERT_INST_NAME, "
				    //+"   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY)) CERT_INST_NAME,"
				    
                    +"  case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
                    +"  from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "

				    +"   P.CREATE_ID, "
				    +"   P.CREATE_NAME, "
				    +"   P.PSC, "
				    +"  P.PROD_CODE, "
				    +"   P.PROD_NAME, "
				    +"   P.STEEL_CODE, "
				    +"   P.STEEL_NAME, "
				    +"   P.STD_CODE, "
				    +"   P.STD_NAME, "
				   // +"   P.DESIGN_KEY, "
				    +"   A.ORDERNO DESIGN_KEY, "
				    +"   P.PSC_DESC, "
				    //+"   Z.fst_judgeresult PHYFSTSRESULT, "
				    +"  case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
				    +"  case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
                    +" else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
				    +"   A.PACTNO 合同号, "
				    +"   (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
				    +"    (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE, to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间,"
				    + " to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间  "
				  +" FROM KCH_TURNOFFLIST@XGCX        T, "
				  +"     SEL_PACTDETAIL_NEW@XGCX     A, "
				  +"     QCM_JHY_SAMPLE_R_ORD P, "
				  +"     ZJ_RESULT_ALL@XGCX          Z "
				+" WHERE  A.ORDERNO = T.ALLOTORDERFORM "
				+"  AND A.PSRNO = P.PSC "
                +"  and to_char(T.PLY, 'fm990.099') = to_char(p.thick, 'fm990.099') "
				+"  and A.STEELCODE= t.DETERMINANTCARDNUMBER "
				+"  and A.STEELCODE = p.steel_code "
		        +"  and t.DETERMINANTCARDNUMBER = p.steel_code "
				
				//+"   AND A.ORDERNO = P.DESIGN_KEY  "
				// +"  and T.ALLOTORDERFORM  = p.design_key"
				+"   and t.ROLLNUMBER = p.batch_no "
				+"   AND Z.BILLETID = T.BILLETID "
				//+"   and p.inspection_lot= Z.BILLETID_JY "
				+"    and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
				+"   AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and d.Validflag is null) "
//				+"   AND Z.CIC_IS_LOCK = '0' "
//				+"   AND Z.PIC_IS_LOCK = '0' "
//				+"   AND Z.SIC_IS_LOCK = '0' "
//				+"   AND Z.DIC_IS_LOCK = '0' "
//				+"   AND Z.SFU_IS_LOCK = '0' "
//				+"   AND Z.SFD_IS_LOCK = '0' "
//				+"   AND Z.SFE_IS_LOCK = '0' "

			    +"   AND P.CERT_INST_CODE != '5000' "
			    + " and P.CERT_INST_CODE != 'IC003' "
			    + " and P.CERT_INST_CODE != 'IC004' "
			    + " and P.CERT_INST_CODE != 'IC001'  "
			    // (or (P.CERT_INST_CODE = 'IC001' AND LENGTH(P.THREE_MEMO)>=6) )
			   // + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社,中国船级社') "
				+ "   and P.CERT_INST_CODE != 'IC010'"
				+ "   and Z.BILLETID_JY like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'H%'"
				+  sql2 
				+ ")t";

			    //+"   AND P.CERT_INST_CODE != '5000' "
			    //+ " and P.CERT_INST_CODE != 'IC003' and P.CERT_INST_CODE != 'IC004' and P.CERT_INST_CODE != 'IC001' "
				//+ "   and P.CERT_INST_CODE != 'IC010'"
				// "  and T.ROLLNUMBER LIKE 'H%'";
				// +"  AND STORAGESTATUS = '501602' ";
            sql +=" where 1=1 ";
			if(!"".equals(batch_no)){
				sql +=" and t.BATCH_NO like '"+batch_no+"%' ";
			}
			
		/*	if(!"".equals(org_name)){
				sql +=" and t.CERT_INST_NAME like '%"+org_name+"%' ";
			}*/
			
			if(!"".equals(prod_namemome)){
				sql +=" and t.PSC_DESC like '"+prod_namemome+"%' ";
			}
			
			if(!"".equals(design_key)){
				sql +=" and t.DESIGN_KEY like '"+design_key+"%' ";
			}
			if(radio.equals("1")){
				sql +=" and t.PHYFSTSRESULT = '1' ";//t.fst_judgeresult
			}else if(radio.equals("0")){
				sql +=" and t.PHYFSTSRESULT <> '1' ";
			}
			if(radioYN.equals("1")){
				sql +=" and t.PHYFSTSRESULT1 = '船检合格' ";
			}else if(radioYN.equals("0")){
				sql +=" and t.PHYFSTSRESULT1 = '待判' ";
			}
			if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
				//sql +=" and t.创建时间 >= '"+starttime+" 00:00:00' ";
				//sql +=" and t.创建时间 <= '"+endtime+" 24:00:00' ";
			}
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm.append(sql);
			try {
				/*ResultSet results=this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString());
				JSONArray jsonArray=resultSetToJsonArry(results);
				cro.setResult(jsonArray); */
				List<HashMap> listEle= mapper.query(sqlucomm.toString());
				if(listEle == null || listEle.size() <=0){
					listEle = QueryKSInfo2(starttime,endtime,batch_no,org_name,design_key,prod_namemome,radio,radioYN);
				}
				cro.setResult(listEle);
			} catch (Exception ex) {
				cro.setV_errCode(-1);
				cro.setV_errMsg("获取数据出错"+ex.getMessage());
				SqlSession.rollback();
			}
			SqlSession.close();
		    return cro;
	}
	
	public List<HashMap> QueryKSInfo2(String starttime,String endtime,String batch_no,String org_name,String design_key,String prod_namemome,String radio,String radioYN) {
		String sql2="";
		if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
			sql2 +=" and t.creattime >= to_date('"+starttime+" 00:00:00', 'yyyy-MM-dd hh24:mi:ss')";
			sql2 +=" and t.creattime <= to_date('"+endtime+" 23:59:59', 'yyyy-MM-dd hh24:mi:ss') ";
		}
		
		if(!"".equals(org_name)){
			sql2 +=" and P.CERT_INST_NAME = '"+org_name+"' ";
		}
		
		String sql ="select * from (SELECT distinct T.BILLETID MATERIAL_NO, "
					+"   '' VESSEL, "
				    +"   P.DELIVERY_STATE_CODE, "
				    +"  P.DELIVERY_STATE_DESC, "
				    +"  TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
				    +"  C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
				    +"  T.STOVENO HEAT_NO, "
				    +"  T.ROLLNUMBER BATCH_NO, "
				    +"   Z.BILLETID_JY INSPECTION_LOT, "
				    +"   T.MOTHERBOARDNUMBER BOARD_NO, "
				    +"   T.PLY THICK, "
				    +"   T.WIDTH, "
				    +"   T.LENGTH, "
				    +"   T.THEORYWEIGHT MAT_WEIGHT, "
				    +"   '4001HB1' PLINE_CODE, "
				    +"   '厚板线' PLINE_NAME, "
				    +"   P.CERT_INST_CODE CERT_INST_CODE, "
                    +"  case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
                    +"  from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
				    +"   P.CREATE_ID, "
				    +"   P.CREATE_NAME, "
				    +"   P.PSC, "
				    +"  P.PROD_CODE, "
				    +"   P.PROD_NAME, "
				    +"   P.STEEL_CODE, "
				    +"   P.STEEL_NAME, "
				    +"   P.STD_CODE, "
				    +"   P.STD_NAME, "
				    +"   A.ORDERNO DESIGN_KEY, "
				    +"   P.PSC_DESC, "
				    +"  case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
				    +"  case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
                    +" else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
				    +"   A.PACTNO 合同号, "
				    +"   (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
				    +"    (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE, to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间,"
				    + " to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间  "
				  +" FROM KCH_TURNOFFLIST@XGCX        T, "
				  +"     SEL_PACTDETAIL_NEW@XGCX     A, "
				  +"     QCM_JHY_SAMPLE_R_ORD P, "
				  +"     ZJ_RESULT_ALL@XGCX          Z "
				+" WHERE  A.ORDERNO = T.ALLOTORDERFORM "
                +"  and to_char(T.PLY, 'fm990.099') = to_char(p.thick, 'fm990.099') "
				+"  and A.STEELCODE= t.DETERMINANTCARDNUMBER "
				+"   and t.ROLLNUMBER = p.batch_no "
				+"   AND Z.BILLETID = T.BILLETID "
				+"   and p.inspection_lot= Z.BILLETID_JY "
				+"    and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
				+"   AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and d.Validflag is null) "
			    +"   AND P.CERT_INST_CODE != '5000' "
			    + " and P.CERT_INST_CODE != 'IC003' "
			    + " and P.CERT_INST_CODE != 'IC004' "
			    + " and (P.CERT_INST_CODE != 'IC001'  or (P.CERT_INST_CODE = 'IC001' AND LENGTH(P.THREE_MEMO)>=6) ) "
			    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社,中国船级社') "
			    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('检测中心,挪威船级社') "
			    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社') "
			    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('韩国船级社,挪威船级社') "
				+ "   and P.CERT_INST_CODE != 'IC010'"
				+ "   and Z.BILLETID_JY like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'H%'"
				+  sql2 
				+ ")t";
            sql +=" where 1=1 and t.CERT_INST_CODE != 'IC001'  "
            		+ " AND T.BATCH_NO NOT IN (SELECT D.BATCH_NO FROM QCM_MAT_CERT_D D where D.CREATE_TIME <= TO_DATE('2024-12-18 00:00:00','YYYY-MM-DD HH24:MI:SS') ) ";
			if(!"".equals(batch_no)){
				sql +=" and t.BATCH_NO like '"+batch_no+"%' ";
			}
			
			if(!"".equals(prod_namemome)){
				sql +=" and t.PSC_DESC like '"+prod_namemome+"%' ";
			}
			
			if(!"".equals(design_key)){
				sql +=" and t.DESIGN_KEY like '"+design_key+"%' ";
			}
			if(radio.equals("1")){
				sql +=" and t.PHYFSTSRESULT = '1' ";
			}else if(radio.equals("0")){
				sql +=" and t.PHYFSTSRESULT <> '1' ";
			}
			if(radioYN.equals("1")){
				sql +=" and t.PHYFSTSRESULT1 = '船检合格' ";
			}else if(radioYN.equals("0")){
				sql +=" and t.PHYFSTSRESULT1 = '待判' ";
			}
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm.append(sql);
			List<HashMap> listEle= mapper.query(sqlucomm.toString());
		    return listEle;
	}
	
	/**
	 * 查询库存数据中板线
	 * @param strDate
	 * @param endDate
	 * @param orgName
	 * @param batch
	 * @param design_key
	 * @return
	 * */
	public CoreReturnObject QueryKczInfo_N(String starttime,String endtime,String batch_no,String org_name,String design_key,String prod_namemome, String radio,String radioYN) {
		String sql2="";
		if (StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)) {	
			String sky=StrSky(starttime,endtime);
			if(sky.equals("FALSE")){
				SqlSession.close();
				cro.setV_errCode(-1);
				cro.setV_errMsg("查询时间不能大于半年,请核实查询时间条件!");
				return cro;
			}
		}
		if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
			sql2 +=" and t.creattime >= to_date('"+starttime+" 00:00:00', 'yyyy-MM-dd hh24:mi:ss')";
			sql2 +=" and t.creattime <= to_date('"+endtime+" 23:59:59', 'yyyy-MM-dd hh24:mi:ss') ";
		}
		if(!"".equals(org_name)){
			sql2 +=" and P.CERT_INST_NAME = '"+org_name+"' ";
		}
            String sql = "select * from ( SELECT distinct T.BILLETID MATERIAL_NO, "
						 +"    '' VESSEL, "
						 +"    P.DELIVERY_STATE_CODE, "
						 +"      P.DELIVERY_STATE_DESC, "
						 +"      TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
						 +"      C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
						 +"      T.STOVENO HEAT_NO, "
						 +"      T.ROLLNUMBER BATCH_NO, "
						 +"      Z.BILLETID_JY INSPECTION_LOT, "
						 +"      T.MOTHERBOARDNUMBER BOARD_NO, "
						 +"      T.PLY THICK, "
						 +"      T.WIDTH, "
						 +"      T.LENGTH, "
						 +"      T.THEORYWEIGHT MAT_WEIGHT, "
						 +"      '4001ZB1' PLINE_CODE, "
						 +"      '中板线' PLINE_NAME, "
						 +"      P.CERT_INST_CODE CERT_INST_CODE, "
						// +"    P.CERT_INST_NAME CERT_INST_NAME, "
						// +"      dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY)) CERT_INST_NAME,"
						 
     +"  case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
     +"  from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
						
						+"      P.CREATE_ID, "
						 +"      P.CREATE_NAME, "
						 +"      P.PSC, "
						 +"      P.PROD_CODE, "
						 +"      P.PROD_NAME, "
						 +"      P.STEEL_CODE, "
						 +"      P.STEEL_NAME, "
						 +"      P.STD_CODE, "
						 +"      P.STD_NAME, "
						 //+"      P.DESIGN_KEY, "
						 +"      A.ORDERNO DESIGN_KEY, "
						 +"     P.PSC_DESC, "
						 //+"     Z.fst_judgeresult PHYFSTSRESULT, "
						 +"  case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
						  +"  case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
                          +" else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
					     
						 +"      A.PACTNO 合同号, "
						 +"        (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
						 +"       (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE,to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间 ,"
						 + "     to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间 "
						 +" FROM KCZ_TURNOFFLIST@XGCX    T, "
						 +"      SEL_PACTDETAIL_NEW@XGCX A, "
						 +"      QCM_JHY_SAMPLE_R_ORD    P, "
						 +"      ZJ_RESULT_ALL@XGCX      Z "
						 +" WHERE A.ORDERNO = T.ORDERNO "
						 +"  AND A.PSRNO = P.PSC and T.PLY = p.thick "
						 //+"  AND A.ORDERNO = P.DESIGN_KEY "
						 //+"  and T.ORDERNO  = p.design_key"
						 +"   and t.ROLLNUMBER = p.batch_no "
						 +"  AND Z.BILLETID = T.BILLETID "
						 //+"  and p.inspection_lot = Z.BILLETID_JY "
						+"   and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
						 +"  AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and d.Validflag is null) "
//						 +"  AND Z.CIC_IS_LOCK = '0' "
//						 +"  AND Z.PIC_IS_LOCK = '0' "
//						 +"  AND Z.SIC_IS_LOCK = '0' "
//						 +"  AND Z.DIC_IS_LOCK = '0' "
//						 +"  AND Z.SFU_IS_LOCK = '0' "
//						 +"  AND Z.SFD_IS_LOCK = '0' "
//						 +"  AND Z.SFE_IS_LOCK = '0' "
                         +"  and t.ISVALID <> '0' "
						 +"   AND P.CERT_INST_CODE != '5000' "
						 + "  and P.CERT_INST_CODE != 'IC003' "
						 + " and P.CERT_INST_CODE != 'IC004' "
						 + " and P.CERT_INST_CODE != 'IC001'   "
						 //(or (P.CERT_INST_CODE = 'IC001' AND LENGTH(P.THREE_MEMO)>=6) )
						 //+ " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社,中国船级社') "
						 + " and P.CERT_INST_CODE != 'IC010'"
						 + "  and  P.INSPECTION_LOT like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'Z%' "
						 + sql2
						 + ")t";

						 //+"   AND P.CERT_INST_CODE != '5000' "
						 //+ "  and P.CERT_INST_CODE != 'IC003' and P.CERT_INST_CODE != 'IC004' and P.CERT_INST_CODE != 'IC001'  "
						// + " and P.CERT_INST_CODE != 'IC010' "
						 // " and T.ROLLNUMBER LIKE 'Z%'";
						 //+"  AND STORAGESTATUS = '501602' ";

            sql +=" where 1=1 ";
			if(!"".equals(batch_no)){
				sql +=" and t.BATCH_NO like '"+batch_no+"%' ";
			}
			
		/*	if(!"".equals(org_name)){
				sql +=" and t.CERT_INST_NAME like '%"+org_name+"%' ";
			}*/
			
			if(!"".equals(prod_namemome)){
				sql +=" and t.PSC_DESC like '"+prod_namemome+"%' ";
			}
			
			if(!"".equals(design_key)){
				sql +=" and t.DESIGN_KEY like '"+design_key+"%' ";
			}
			if(radio.equals("1")){
				sql +=" and t.PHYFSTSRESULT = '1' ";//fst_judgeresult
			}else if(radio.equals("0")){
				sql +=" and t.PHYFSTSRESULT <> '1' ";
			}
			if(radioYN.equals("1")){
				sql +=" and t.PHYFSTSRESULT1 = '船检合格' ";
			}else if(radioYN.equals("0")){
				sql +=" and t.PHYFSTSRESULT1 = '待判' ";
			}
			if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
				sql +=" and t.创建时间 >= '"+starttime+" 00:00:00' ";
				sql +=" and t.创建时间 <= '"+endtime+" 24:00:00' ";
			}
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm.append(sql);
			try {
				/*ResultSet results=this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString());
				JSONArray jsonArray=resultSetToJsonArry(results);
				cro.setResult(jsonArray); */
				List<HashMap> listEle= mapper.query(sqlucomm.toString());
				if(listEle == null || listEle.size() <=0){
					listEle = QueryKczInfo2(starttime,endtime,batch_no,org_name,design_key,prod_namemome,radio,radioYN);
				}
				cro.setResult(listEle);
			} catch (Exception ex) {
				cro.setV_errCode(-1);
				cro.setV_errMsg("获取数据出错"+ex.getMessage());
				SqlSession.rollback();
			}
			SqlSession.close();
		    return cro;
	}
	
	public List<HashMap> QueryKczInfo2(String starttime,String endtime,String batch_no,String org_name,String design_key,String prod_namemome, String radio,String radioYN) {
		String sql2="";
		if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
			sql2 +=" and t.creattime >= to_date('"+starttime+" 00:00:00', 'yyyy-MM-dd hh24:mi:ss')";
			sql2 +=" and t.creattime <= to_date('"+endtime+" 23:59:59', 'yyyy-MM-dd hh24:mi:ss') ";
		}
		if(!"".equals(org_name)){
			sql2 +=" and P.CERT_INST_NAME = '"+org_name+"' ";
		}
            String sql = "select * from ( SELECT distinct T.BILLETID MATERIAL_NO, "
						 +"    '' VESSEL, "
						 +"    P.DELIVERY_STATE_CODE, "
						 +"      P.DELIVERY_STATE_DESC, "
						 +"      TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
						 +"      C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
						 +"      T.STOVENO HEAT_NO, "
						 +"      T.ROLLNUMBER BATCH_NO, "
						 +"      Z.BILLETID_JY INSPECTION_LOT, "
						 +"      T.MOTHERBOARDNUMBER BOARD_NO, "
						 +"      T.PLY THICK, "
						 +"      T.WIDTH, "
						 +"      T.LENGTH, "
						 +"      T.THEORYWEIGHT MAT_WEIGHT, "
						 +"      '4001ZB1' PLINE_CODE, "
						 +"      '中板线' PLINE_NAME, "
						 +"      P.CERT_INST_CODE CERT_INST_CODE, "
     +"  case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
     +"  from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
						+"      P.CREATE_ID, "
						 +"      P.CREATE_NAME, "
						 +"      P.PSC, "
						 +"      P.PROD_CODE, "
						 +"      P.PROD_NAME, "
						 +"      P.STEEL_CODE, "
						 +"      P.STEEL_NAME, "
						 +"      P.STD_CODE, "
						 +"      P.STD_NAME, "
						 +"      A.ORDERNO DESIGN_KEY, "
						 +"     P.PSC_DESC, "
						 +"  case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
						  +"  case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
                          +" else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
					     
						 +"      A.PACTNO 合同号, "
						 +"        (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
						 +"       (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE,to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间 ,"
						 + "     to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间 "
						 +" FROM KCZ_TURNOFFLIST@XGCX    T, "
						 +"      SEL_PACTDETAIL_NEW@XGCX A, "
						 +"      QCM_JHY_SAMPLE_R_ORD    P, "
						 +"      ZJ_RESULT_ALL@XGCX      Z "
						 +" WHERE A.ORDERNO = T.ORDERNO "
						 + " and to_char(T.PLY, 'fm990.099') = to_char(p.thick, 'fm990.099')"
						 +"  and t.ROLLNUMBER = p.batch_no "
						 +"  AND Z.BILLETID = T.BILLETID "
						 +"  and p.inspection_lot = Z.BILLETID_JY "
						+"   and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
						 +"  AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and d.Validflag is null) "
                         +"  and t.ISVALID <> '0' "
						 +"   AND P.CERT_INST_CODE != '5000' "
						 + "  and P.CERT_INST_CODE != 'IC003' "
						 + " and P.CERT_INST_CODE != 'IC004' "
						 + " and (P.CERT_INST_CODE != 'IC001'  or (P.CERT_INST_CODE = 'IC001' AND LENGTH(P.THREE_MEMO)>=6) ) "
						    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社,中国船级社') "
						    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('检测中心,挪威船级社') "
						    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社') "
						    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('韩国船级社,挪威船级社') "
						 + " and P.CERT_INST_CODE != 'IC010'"
						 + "  and  P.INSPECTION_LOT like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'Z%' "
						 + sql2
						 + ")t";

            sql +=" where 1=1 and t.CERT_INST_CODE != 'IC001' "
            		+ " AND T.BATCH_NO NOT IN (SELECT D.BATCH_NO FROM QCM_MAT_CERT_D D where D.CREATE_TIME <= TO_DATE('2024-12-18 00:00:00','YYYY-MM-DD HH24:MI:SS') ) ";
			if(!"".equals(batch_no)){
				sql +=" and t.BATCH_NO like '"+batch_no+"%' ";
			}
			
			if(!"".equals(prod_namemome)){
				sql +=" and t.PSC_DESC like '"+prod_namemome+"%' ";
			}
			
			if(!"".equals(design_key)){
				sql +=" and t.DESIGN_KEY like '"+design_key+"%' ";
			}
			if(radio.equals("1")){
				sql +=" and t.PHYFSTSRESULT = '1' ";
			}else if(radio.equals("0")){
				sql +=" and t.PHYFSTSRESULT <> '1' ";
			}
			if(radioYN.equals("1")){
				sql +=" and t.PHYFSTSRESULT1 = '船检合格' ";
			}else if(radioYN.equals("0")){
				sql +=" and t.PHYFSTSRESULT1 = '待判' ";
			}
			if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
				sql +=" and t.创建时间 >= '"+starttime+" 00:00:00' ";
				sql +=" and t.创建时间 <= '"+endtime+" 24:00:00' ";
			}
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm.append(sql);
			
			List<HashMap> listEle= mapper.query(sqlucomm.toString());
		
		    return listEle;
	}
	
	public CoreReturnObject QueryKSAllInfo(String starttime,String endtime,String batch_no,String org_name,String design_key,String prod_namemome, String radio,String radioYN){
		String sql2="";
		String sql3="";
		if (StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)) {	
			String sky=StrSky(starttime,endtime);
			if(sky.equals("FALSE")){
				SqlSession.close();
				cro.setV_errCode(-1);
				cro.setV_errMsg("查询时间不能大于半年,请核实查询时间条件!");
				return cro;
			}
		}
		if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
			sql2 +=" and t.creattime >= to_date('"+starttime+" 00:00:00', 'yyyy-MM-dd hh24:mi:ss')";
			sql2 +=" and t.creattime <= to_date('"+endtime+" 23:59:59', 'yyyy-MM-dd hh24:mi:ss') ";
		}
		if(!"".equals(org_name)){
			sql2 +=" and P.CERT_INST_NAME = '"+org_name+"' ";
		}
		
		sql3 +=" where 1=1 ";
		if(!"".equals(batch_no)){
			sql3 +=" and t.BATCH_NO like '"+batch_no+"%' ";
		}
		if(!"".equals(prod_namemome)){
			sql3 +=" and t.PSC_DESC like '"+prod_namemome+"%' ";
		}
		
		if(!"".equals(design_key)){
			sql3 +=" and t.DESIGN_KEY like '"+design_key+"%' ";
		}
		if(radio.equals("1")){
			sql3 +=" and t.PHYFSTSRESULT = '1' ";
		}else if(radio.equals("0")){
			sql3 +=" and t.PHYFSTSRESULT <> '1' ";
		}
		if(radioYN.equals("1")){
			sql3 +=" and t.PHYFSTSRESULT1 = '船检合格' ";
		}else if(radioYN.equals("0")){
			sql3 +=" and t.PHYFSTSRESULT1 = '待判' ";
		}
		if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
			sql3 +=" and t.创建时间 >= '"+starttime+" 00:00:00' ";
			sql3 +=" and t.创建时间 <= '"+endtime+" 24:00:00' ";
		}
					
            String sql = "select * from ( SELECT distinct T.BILLETID MATERIAL_NO, "
						 +"      '' VESSEL, "
						 +"      P.DELIVERY_STATE_CODE, "
						 +"      P.DELIVERY_STATE_DESC, "
						 +"      TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
						 +"      C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
						 +"      T.STOVENO HEAT_NO, "
						 +"      T.ROLLNUMBER BATCH_NO, "
						 +"      Z.BILLETID_JY INSPECTION_LOT, "
						 +"      T.MOTHERBOARDNUMBER BOARD_NO, "
						 +"      T.PLY THICK, "
						 +"      T.WIDTH, "
						 +"      T.LENGTH, "
						 +"      T.THEORYWEIGHT MAT_WEIGHT, "
						 +"      '4001ZB1' PLINE_CODE, "
						 +"      '中板线' PLINE_NAME, "
						 +"      P.CERT_INST_CODE CERT_INST_CODE, "
					     +"  case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
					     +"  from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
						 +"      P.CREATE_ID, "
						 +"      P.CREATE_NAME, "
						 +"      P.PSC, "
						 +"      P.PROD_CODE, "
						 +"      P.PROD_NAME, "
						 +"      P.STEEL_CODE, "
						 +"      P.STEEL_NAME, "
						 +"      P.STD_CODE, "
						 +"      P.STD_NAME, "
						 +"      A.ORDERNO DESIGN_KEY, "
						 +"      P.PSC_DESC, "
						 +"   case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
						 +"   case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
                         +"   else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
						 +"      A.PACTNO 合同号, "
						 +"      (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
						 +"      (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE,to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间 ,"
						 +"      to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间 "
						 +"  FROM KCZ_TURNOFFLIST@XGCX    T, "
						 +"      SEL_PACTDETAIL_NEW@XGCX A, "
						 +"      QCM_JHY_SAMPLE_R_ORD    P, "
						 +"      ZJ_RESULT_ALL@XGCX      Z "
						 +"  WHERE A.ORDERNO = T.ORDERNO "
						 +"  AND A.PSRNO = P.PSC and T.PLY = p.thick "
						 +"  and t.ROLLNUMBER = p.batch_no "
						 +"  AND Z.BILLETID = T.BILLETID "
						 +"  and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
						 +"  AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and (d.Validflag is null or d.validflag='0')) "
                         +"  and t.ISVALID <> '0' "
						 +"  AND P.CERT_INST_CODE != '5000' "
						 +"  and P.CERT_INST_CODE != 'IC003' "
						 +"  and P.CERT_INST_CODE != 'IC004' "
						 +"  and P.CERT_INST_CODE != 'IC001'   "
						 +"  and P.CERT_INST_CODE != 'IC010'"
						 +"  and  P.INSPECTION_LOT like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'Z%' "
						 + sql2
						 + ")t"+sql3
						 
                      +" union "
                      +" select * from ( SELECT distinct T.BILLETID MATERIAL_NO, "
					 +"      '' VESSEL, "
					 +"      P.DELIVERY_STATE_CODE, "
					 +"      P.DELIVERY_STATE_DESC, "
					 +"      TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
					 +"      C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
					 +"      T.STOVENO HEAT_NO, "
					 +"      T.ROLLNUMBER BATCH_NO, "
					 +"      Z.BILLETID_JY INSPECTION_LOT, "
					 +"      T.MOTHERBOARDNUMBER BOARD_NO, "
					 +"      T.PLY THICK, "
					 +"      T.WIDTH, "
					 +"      T.LENGTH, "
					 +"      T.THEORYWEIGHT MAT_WEIGHT, "
					 +"      '4001ZB1' PLINE_CODE, "
					 +"      '中板线' PLINE_NAME, "
					 +"      P.CERT_INST_CODE CERT_INST_CODE, "
				     +"  case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
				     +"  from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
					 +"      P.CREATE_ID, "
					 +"      P.CREATE_NAME, "
					 +"      P.PSC, "
					 +"      P.PROD_CODE, "
					 +"      P.PROD_NAME, "
					 +"      P.STEEL_CODE, "
					 +"      P.STEEL_NAME, "
					 +"      P.STD_CODE, "
					 +"      P.STD_NAME, "
					 +"      A.ORDERNO DESIGN_KEY, "
					 +"      P.PSC_DESC, "
					 +"   case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
					 +"   case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
                     +"   else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
					 +"      A.PACTNO 合同号, "
					 +"      (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
					 +"      (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE,to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间 ,"
					 +"      to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间 "
					 +" FROM KCZ_TURNOFFLIST@XGCX    T, "
					 +"      SEL_PACTDETAIL_NEW@XGCX A, "
					 +"      QCM_JHY_SAMPLE_R_ORD    P, "
					 +"      ZJ_RESULT_ALL@XGCX      Z "
					 +" WHERE A.ORDERNO = T.ORDERNO "
					 +"  and to_char(T.PLY, 'fm990.099') = to_char(p.thick, 'fm990.099')"
					 +"  and t.ROLLNUMBER = p.batch_no "
					 +"  AND Z.BILLETID = T.BILLETID "
					 +"  and p.inspection_lot = Z.BILLETID_JY "
					 +"  and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
					 +"  AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and (d.Validflag is null or d.validflag='0')) "
                     +"  and t.ISVALID <> '0' "
					 +"  AND P.CERT_INST_CODE != '5000' "
					 + " and P.CERT_INST_CODE != 'IC003' "
					 + " and P.CERT_INST_CODE != 'IC004' "
					 + " and (P.CERT_INST_CODE != 'IC001'  or (P.CERT_INST_CODE = 'IC001' AND LENGTH(P.THREE_MEMO)>=6) ) "
					    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社,中国船级社') "
					    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('检测中心,挪威船级社') "
					    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社') "
					    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('韩国船级社,挪威船级社') "
					 + "  and P.CERT_INST_CODE != 'IC010'"
					 + "  and  P.INSPECTION_LOT like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'Z%' "
					 + sql2
					 + ")t"+sql3
					 +" and t.CERT_INST_CODE != 'IC001' "
					 +" AND T.MATERIAL_NO NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where D.CREATE_TIME <= TO_DATE('2024-12-18 00:00:00','YYYY-MM-DD HH24:MI:SS') )";
            
            sql +=" union select * from (SELECT distinct T.BILLETID MATERIAL_NO, "
					+"  '' VESSEL, "
				    +"  P.DELIVERY_STATE_CODE, "
				    +"  P.DELIVERY_STATE_DESC, "
				    +"  TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
				    +"  C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
				    +"  T.STOVENO HEAT_NO, "
				    +"  T.ROLLNUMBER BATCH_NO, "
				    +"  Z.BILLETID_JY INSPECTION_LOT, "
				    +"  T.MOTHERBOARDNUMBER BOARD_NO, "
				    +"  T.PLY THICK, "
				    +"  T.WIDTH, "
				    +"  T.LENGTH, "
				    +"  T.THEORYWEIGHT MAT_WEIGHT, "
				    +"  '4001HB1' PLINE_CODE, "
				    +"  '厚板线' PLINE_NAME, "
				    +"  P.CERT_INST_CODE CERT_INST_CODE, "
                    +"  case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
                    +"  from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
				    +"  P.CREATE_ID, "
				    +"  P.CREATE_NAME, "
				    +"  P.PSC, "
				    +"  P.PROD_CODE, "
				    +"  P.PROD_NAME, "
				    +"  P.STEEL_CODE, "
				    +"  P.STEEL_NAME, "
				    +"  P.STD_CODE, "
				    +"  P.STD_NAME, "
				    +"  A.ORDERNO DESIGN_KEY, "
				    +"  P.PSC_DESC, "
				    +"  case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
				    +"  case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
                    +"  else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
				    +"  A.PACTNO 合同号, "
				    +"  (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
				    +"  (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE, to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间,"
				    +"  to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间  "
				  +" FROM KCH_TURNOFFLIST@XGCX        T, "
				  +"     SEL_PACTDETAIL_NEW@XGCX     A, "
				  +"     QCM_JHY_SAMPLE_R_ORD P, "
				  +"     ZJ_RESULT_ALL@XGCX          Z "
				+" WHERE  A.ORDERNO = T.ALLOTORDERFORM "
				+"  AND A.PSRNO = P.PSC "
                +"  and to_char(T.PLY, 'fm990.099') = to_char(p.thick, 'fm990.099') "
				+"  and A.STEELCODE= t.DETERMINANTCARDNUMBER "
				+"  and A.STEELCODE = p.steel_code "
		        +"  and t.DETERMINANTCARDNUMBER = p.steel_code "
				+"   and t.ROLLNUMBER = p.batch_no "
				+"   AND Z.BILLETID = T.BILLETID "
				+"    and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
				+"   AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and (d.Validflag is null or d.validflag='0')) "
			    +"   AND P.CERT_INST_CODE != '5000' "
			    + " and P.CERT_INST_CODE != 'IC003' "
			    + " and P.CERT_INST_CODE != 'IC004' "
			    + " and P.CERT_INST_CODE != 'IC001'  "
				+ "   and P.CERT_INST_CODE != 'IC010'"
				+ "   and Z.BILLETID_JY like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'H%'"
				+  sql2 
				+ ")t"
				+ sql3
				
				+"union"
				+" select * from (SELECT distinct T.BILLETID MATERIAL_NO, "
				+" '' VESSEL, "
			    +" P.DELIVERY_STATE_CODE, "
			    +" P.DELIVERY_STATE_DESC, "
			    +" TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
			    +" C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
			    +" T.STOVENO HEAT_NO, "
			    +" T.ROLLNUMBER BATCH_NO, "
			    +" Z.BILLETID_JY INSPECTION_LOT, "
			    +" T.MOTHERBOARDNUMBER BOARD_NO, "
			    +" T.PLY THICK, "
			    +" T.WIDTH, "
			    +" T.LENGTH, "
			    +" T.THEORYWEIGHT MAT_WEIGHT, "
			    +" '4001HB1' PLINE_CODE, "
			    +" '厚板线' PLINE_NAME, "
			    +" P.CERT_INST_CODE CERT_INST_CODE, "
                +" case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
                +" from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
			    +" P.CREATE_ID, "
			    +" P.CREATE_NAME, "
			    +" P.PSC, "
			    +" P.PROD_CODE, "
			    +" P.PROD_NAME, "
			    +" P.STEEL_CODE, "
			    +" P.STEEL_NAME, "
			    +" P.STD_CODE, "
			    +" P.STD_NAME, "
			    +" A.ORDERNO DESIGN_KEY, "
			    +" P.PSC_DESC, "
			    +" case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
			    +" case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
                +" else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
			    +" A.PACTNO 合同号, "
			    +" (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
			    +" (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE, to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间,"
			    +" to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间  "
			  +"   FROM KCH_TURNOFFLIST@XGCX        T, "
			  +"     SEL_PACTDETAIL_NEW@XGCX     A, "
			  +"     QCM_JHY_SAMPLE_R_ORD P, "
			  +"     ZJ_RESULT_ALL@XGCX          Z "
			+"  WHERE  A.ORDERNO = T.ALLOTORDERFORM "
            +"  and to_char(T.PLY, 'fm990.099') = to_char(p.thick, 'fm990.099') "
			+"  and A.STEELCODE= t.DETERMINANTCARDNUMBER "
			+"  and t.ROLLNUMBER = p.batch_no "
			+"  AND Z.BILLETID = T.BILLETID "
			+"  and p.inspection_lot= Z.BILLETID_JY "
			+"  and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
			+"  AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and (d.Validflag is null or d.validflag='0')) "
		    +"  AND P.CERT_INST_CODE != '5000' "
		    + " and P.CERT_INST_CODE != 'IC003' "
		    + " and P.CERT_INST_CODE != 'IC004' "
		    + " and (P.CERT_INST_CODE != 'IC001'  or (P.CERT_INST_CODE = 'IC001' AND LENGTH(P.THREE_MEMO)>=6) ) "
		    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社,中国船级社') "
		    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('检测中心,挪威船级社') "
		    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社') "
		    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('韩国船级社,挪威船级社') "
			+ " and P.CERT_INST_CODE != 'IC010'"
			+ " and Z.BILLETID_JY like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'H%'"
			+  sql2 
			+ ")t "+sql3
			+" and t.CERT_INST_CODE != 'IC001' "
			+" AND T.MATERIAL_NO NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where D.CREATE_TIME <= TO_DATE('2024-12-18 00:00:00','YYYY-MM-DD HH24:MI:SS') )";
		
            StringBuffer sqlucomm = new StringBuffer();
		    sqlucomm.append(sql);
		   // System.out.println(sql);
		try {
			List<HashMap> listEle= mapper.query(sqlucomm.toString());
			cro.setResult(listEle); 
		} catch (Exception ex) {
			cro.setV_errCode(-1);
			cro.setV_errMsg("获取数据出错"+ex.getMessage());
			SqlSession.rollback();
		}
		SqlSession.close();
		return cro;
	}
	
	/**
	 * 导出 查询库存数据
	 * @param strDate
	 * @param endDate
	 * @param orgName
	 * @param batch
	 * @param design_key
	 * @return
	 */
	public CoreReturnObject KsQueryExcel(String starttime,String endtime,String batch_no,String org_name,String design_key,String prod_namemome,String pline_name) {

        String sql = " select count (1) 块数, "
				    +"   t.HEAT_NO 冶炼炉号, "
				    +"   t.BATCH_NO 轧编号, "
				    +"   t.STEEL_NAME 牌号, "
				    +"   t.THICK 厚度, "
				    +"   sum(t.MAT_WEIGHT) 重量, "
				    +"   t.DELIVERY_STATE_DESC 交货状态, "
				    +"   t.PHYFSTSRESULT1 是否已船检合格, t.memo 订单备注 "
				    +"   from ( "
				 +" select t.HEAT_NO, "
				 +"        t.BATCH_NO, "
				 +"        case when "
				 +"        (select max(nvl(MAIN_STEEL_CODE,'1')) from QCM_ORD_DESIGN_STD_PIC_SMP s where s.design_key = t.design_key) = t.STEEL_NAME "
                 +"        then t.STEEL_NAME else t.STEEL_NAME || '(副)' end STEEL_NAME, "
				 +"        t.THICK, "
				 +"        nvl(t.mat_weight2, to_char(t.MAT_WEIGHT, 'fm990.099')) MAT_WEIGHT, "
				 +"        t.DELIVERY_STATE_DESC, "
				 +"  decode((SELECT count(1) "
		         +"           from zj_result_all@xgcx z  "
		         +"             where z.JUDGERESULT = '1' and z.BILLETID = T.Material_No)   "
		         +"      ,'0','否','是') PHYFSTSRESULT1, "
				 +"   (select me.memo from QCM_ORD_DESIGN_MEMO me where me.design_key = t.design_key and rownum=1) memo "
				 +" from QCM_MAT_CERT_D t "
				 +" left join QCM_MAT_CERT_M m "
				 +"  on t.cert_no = m.cert_no "
				 +" where 1 = 1 and (t.VALIDFLAG is null or t.VALIDFLAG = '1' ) ";
				

        if(!"".equals(pline_name)&& !"全部".equals(pline_name)){
			sql +=" and t.PLINE_NAME like '"+pline_name+"%' ";
		}
        
        if(!"".equals(org_name) && !"全部".equals(org_name)){
    			sql +=" and m.CERT_INST_NAME like '%"+org_name+"%' ";
    		}
        
        if(!"".equals(design_key) && design_key!=null){
			sql +=" and t.design_key like '"+design_key+"%' ";
		}
        
        if(!"".equals(prod_namemome) && prod_namemome!=null){
			sql +=" and t.psc_desc like '"+prod_namemome+"%' ";
		}
        
		if(!"".equals(starttime)){
			sql += " and t.CREATE_TIME >= to_date('"+starttime+" 00:00:00','yyyy-mm-dd,hh24:mi:ss')";
		}
		if(!"".equals(endtime)){
			sql += " and t.CREATE_TIME <= to_date('"+endtime+" 23:59:59','yyyy-mm-dd,hh24:mi:ss')";
		}
		
        
        sql +=" order by m.CREATE_TIME desc ) t "
				  +" group by  "
				  +"     t.HEAT_NO, "
				  +"     t.BATCH_NO, "
				  +"     t.STEEL_NAME, "
				  +"     t.THICK, "
				  +"     t.DELIVERY_STATE_DESC, "
				  +"     t.PHYFSTSRESULT1, t.memo "
				  + " order by t.BATCH_NO"	;
				
		StringBuffer sqlucomm = new StringBuffer();
		sqlucomm.append(sql);
			
			try {
				/*ResultSet results=this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString());
				JSONArray jsonArray=resultSetToJsonArry(results);
				cro.setResult(jsonArray); */
				List<HashMap> listEle= mapper.query(sqlucomm.toString());
				cro.setResult(listEle);
			} catch (Exception ex) {
				cro.setV_errCode(-1);
				cro.setV_errMsg("导出失败！"+ex.getMessage());
				SqlSession.rollback();
			}
			SqlSession.close();
		    return cro;
	}
	
	/**
	  *ResultSet转换为JSON数组
	  * 
	  * @param ResultSet
	  * @return JSONArray
	  */
	    public static JSONArray resultSetToJsonArry(ResultSet rs) throws SQLException, JSONException {
	        JSONArray array = new JSONArray();
	        ResultSetMetaData metaData = rs.getMetaData();
	        int columnCount = metaData.getColumnCount();
	        while (rs.next()) {
	            JSONObject jsonObj = new JSONObject();
	            for (int i = 1; i <= columnCount; i++) {
	                String columnName = metaData.getColumnLabel(i);
	                String value = rs.getString(columnName);
	                jsonObj.put(columnName, value);
	            }
	            array.add(jsonObj);
	        }
	        return array;
	    }
	    
	    
		/**
		 * 认证机构
		 * @param strDate
		 * @param endDate
		 * @param orgName
		 * @param batch
		 * @param design_key
		 * @return
		 */
		public CoreReturnObject getCertInstCode() {
	        String sql = " select CERT_INST_CODE,CERT_INST_NAME from qcm_jhy_sample_r_ord r "
                    +"  group by CERT_INST_CODE,CERT_INST_NAME order by r.cert_inst_code ";
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm.append(sql);
				
				try {
					List<HashMap> listEle= mapper.query(sqlucomm.toString());
					cro.setResult(listEle);
				} catch (Exception ex) {
					cro.setV_errCode(-1);
					cro.setV_errMsg("查询认证机构失败！"+ex.getMessage());
					SqlSession.rollback();
				}
				SqlSession.close();
			    return cro;
		}

		/**
		 * 查询第三方见证数据
		 * @return
		 */
		public CoreReturnObject getMatWitneD(HashMap parmas) {
			try {
	        String sql = "select  "
       +" t1.matwitne, "
       +" t1.design_key, "
       +" t1.steel_code, "
       +" t1.steel_name, "
       +" t1.thick, "
       +" t1.heat_no, "
       +" t1.batch_no, "
       +" t1.material_no, "
       +" t1.smp_no, "
       +" t1.specimen_no, "
       +" t1.inspection_lot, "
       +" t1.freq_code, "
       +" t1.freq_name, "
       +" t1.smp_type_code, "
       +" t1.smp_type_name, "
       +" t1.smp_location_code, "
       +" t1.smp_location, "
       +" t1.board_no, "
       +" t1.pline_code, "
       +" t1.pline_name, "
       +" t1.process_nos, "
       +" t1.phy_item, "
       +" t1.smp_catg, "
       +" t1.cert_inst_code, "
       +" t1.cert_inst_name, "
       +" t1.psc, "
       +" t1.psc_desc, "
       +" t1.delivery_state_code, "
       +" t1.delivery_state_desc, "
       +" t1.width, "
       +" t1.length, "
       +" t1.msc_pline, "
       +" t1.process_code, "
       +" t1.prod_code, "
       +" t1.prod_name, "
       +" t1.std_code, "
       +" t1.std_name, "
       +" t1.three_memo, "
       +" t1.memo, "
       +" t1.improve_memo, "
       +" t1.create_time "
 +" from ( select QCM_MAT_WITNE(r.design_key,r.batch_no,r.inspection_lot) MATWITNE, r.design_key, "
	        		+"    r.steel_code, "
	        		+"    r.steel_name, "
	        		+"    r.thick, "
	        		+"    t.heat_no, "
	        		+"    t.batch_no, "
	        		+"    t.material_no, "
	        		+"    t.smp_no, "
	        		+"    t.specimen_no, "
	        		+"    t.inspection_lot, "
	        		+"    t.freq_code, "
	        		+"    t.freq_name, "
	        		+"    t.smp_type_code, "
	        		+"    t.smp_type_name, "
	        		+"    t.smp_location_code, "
	        		+"    t.smp_location, "
	        		+"    t.board_no, "
	        		+"    t.pline_code, "
	        		+"    t.pline_name, "
	        		+"    t.process_nos, "
	        		+"    t.phy_item, "
	        		+"    m.smp_catg, "
	        		+"    m.cert_inst_code, "
	        		+"    m.cert_inst_name, "
	        		+"    r.psc, "
	        		+"    r.psc_desc, "
	        		+"    r.delivery_state_code, "
	        		+"    r.delivery_state_desc, "
	        		+"    r.width, "
	        		+"    r.length, "
	        		+"    r.msc_pline, "
	        		+"    r.process_code, "
	        		+"    r.prod_code, "
	        		+"    r.prod_name, "
	        		+"    r.std_code, "
	        		+"    r.std_name, "
	        		+"    r.three_memo, "
	        		+"    t.memo, "
	        		+"    t.improve_memo, "
	        		+"    to_char(t.create_time,'yyyy-MM-dd hh24:mi:ss') create_time "
	        		+" from qcm_jhy_sample_consign_d t, "
	        		+"   qcm_jhy_sample_consign_m m, "
				    +"   qcm_jhy_sample_r_ord     r "
				 +" where t.smp_no = m.smp_no "
				   +" and t.smp_no = r.smp_no "
				   +" and m.smp_no = r.smp_no "
				   +" and r.CERT_INST_CODE in ('IC013','IC012','IC026','IC011','IC014','IC015','IC016','IC017','IC018','IC019','IC020','IC021','IC022','IC023','IC024','IC026') "
				   +" and t.status not in ('3','6','5') "
				   +" and t.freq_code <> 'D'  and t.Smp_Type_Code='0' "
				   +" and t.Mat_Witne is null ";
				

			if(StringUtils.isNotBlank(parmas.get("STARTTIME").toString())&& StringUtils.isNotBlank(parmas.get("ENDTIME").toString())){
				sql +=" and t.create_time >= to_date('"+parmas.get("STARTTIME")+" 00:00:00', 'yyyy-MM-dd hh24:mi:ss')";
				sql +=" and t.create_time <= to_date('"+parmas.get("ENDTIME")+" 23:59:59', 'yyyy-MM-dd hh24:mi:ss') ";
			}
			
			if (StringUtils.isNotBlank(parmas.get("CERT_INST_NAME").toString())) {
				sql +=" and m.cert_inst_name = '"+parmas.get("CERT_INST_NAME")+"' ";
			}
			
			if (StringUtils.isNotBlank(parmas.get("BATCH_NO").toString())) {
				sql +=" and t.BATCH_NO = '"+parmas.get("BATCH_NO")+"' ";
			}
			
			if (StringUtils.isNotBlank(parmas.get("DESIGN_KEY").toString())) {
				sql +=" and r.DESIGN_KEY like '"+parmas.get("DESIGN_KEY")+"%' ";
			}
			
			if (StringUtils.isNotBlank(parmas.get("STEEL_CODE").toString())) {
				sql +=" and r.STEEL_CODE like '"+parmas.get("STEEL_CODE")+"%' ";
			}
			
			if (parmas.get("PLINENAME").toString().equals("全部")) {
				sql +=" and t.pline_code in ('ZB1','HB1') ";
			}else{
				sql +=" and t.pline_code ='"+parmas.get("PLINENAME")+"' ";
			}
			sql +=")t1 where 1=1 ";
			if (parmas.get("RADIO").toString().equals("1") || parmas.get("RADIO").toString().equals("0")) {
				sql +=" and  t1.matwitne ='"+parmas.get("RADIO")+"' ";
			}
					List<HashMap> listEle= mapper.query(sql.toString());
					cro.setResult(listEle);
				} catch (Exception ex) {
					cro.setV_errCode(-1);
					cro.setV_errMsg("查询失败！"+ex.getMessage());
					SqlSession.rollback();
				}
				SqlSession.close();
			    return cro;
		}
		
		
		/**
		 * 查询第三方见证申报数据
		 * @return
		 */
		public CoreReturnObject getQcmMatWitneD(HashMap parmas) {
			try {
	        String sql = " select '1' matwitne,design_key, "
			       +" steel_code, "
			       +" steel_name, "
			       +" thick, "
			       +" heat_no, "
			       +" batch_no, "
			       +" material_no, "
			       +" smp_no, "
			       +" specimen_no, "
			       +" inspection_lot, "
			       +" freq_code, "
			       +" freq_name, "
			       +" smp_type_code, "
			       +" smp_type_name, "
			       +" smp_location_code, "
			       +" smp_location, "
			       +" board_no, "
			       +" pline_code, "
			       +" pline_name, "
			       +" process_nos, "
			       +" phy_item, "
			       +" smp_catg, "
			       +" cert_inst_code, "
			       +" cert_inst_name, "
			       +" psc, "
			       +" psc_desc, "
			       +" delivery_state_code, "
			       +" delivery_state_desc, "
			       +" width, "
			       +" length, "
			       +" msc_pline, "
			       +" process_code, "
			       +" prod_code, "
			       +" prod_name, "
			       +" std_code, "
			       +" std_name, "
			       +" three_memo, "
			       +" memo, "
			       +" improve_memo, "
			       +" to_char(create_time,'yyyy-MM-dd hh24:mi:ss') create_time, "
			       +" create_name "
			  +" from qcm_mat_witne_d t where 1=1 ";
				

			if(StringUtils.isNotBlank(parmas.get("STARTTIME").toString())&& StringUtils.isNotBlank(parmas.get("ENDTIME").toString())){
				sql +=" and t.create_time >= to_date('"+parmas.get("STARTTIME")+" 00:00:00', 'yyyy-MM-dd hh24:mi:ss')";
				sql +=" and t.create_time <= to_date('"+parmas.get("ENDTIME")+" 23:59:59', 'yyyy-MM-dd hh24:mi:ss') ";
			}
			
			if (StringUtils.isNotBlank(parmas.get("CERT_INST_NAME").toString())) {
				sql +=" and t.cert_inst_name = '"+parmas.get("CERT_INST_NAME")+"' ";
			}
			
			if (StringUtils.isNotBlank(parmas.get("BATCH_NO").toString())) {
				sql +=" and t.BATCH_NO = '"+parmas.get("BATCH_NO")+"' ";
			}
			
			if (StringUtils.isNotBlank(parmas.get("DESIGN_KEY").toString())) {
				sql +=" and t.DESIGN_KEY = '"+parmas.get("DESIGN_KEY")+"' ";
			}
			
			if (StringUtils.isNotBlank(parmas.get("STEEL_CODE").toString())) {
				sql +=" and t.STEEL_CODE = '"+parmas.get("STEEL_CODE")+"' ";
			}
			
			if (parmas.get("PLINENAME").toString().equals("全部")) {
				sql +=" and t.pline_code in ('ZB1','HB1') ";
			}else{
				sql +=" and t.pline_code ='"+parmas.get("PLINENAME")+"' ";
			}
					List<HashMap> listEle= mapper.query(sql.toString());
					cro.setResult(listEle);
				} catch (Exception ex) {
					cro.setV_errCode(-1);
					cro.setV_errMsg("查询失败！"+ex.getMessage());
					SqlSession.rollback();
				}
				SqlSession.close();
			    return cro;
		}
		
		//新
		/**
		 * 查询库存数据厚板线
		 * @param strDate
		 * @param endDate
		 * @param orgName
		 * @param batch
		 * @param design_key
		 * @return
		 */

		public CoreReturnObject QueryKSInfo(String starttime,String endtime,String batch_no,String org_name,String design_key,String prod_namemome,String radio,String radioYN) {
			String sql2="";
			String sql3="";
			if (StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)) {	
				String sky=StrSky(starttime,endtime);
				if(sky.equals("FALSE")){
					SqlSession.close();
					cro.setV_errCode(-1);
					cro.setV_errMsg("查询时间不能大于半年,请核实查询时间条件!");
					return cro;
				}
			}
			if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
				sql2 +=" and t.creattime >= to_date('"+starttime+" 00:00:00', 'yyyy-MM-dd hh24:mi:ss')";
				sql2 +=" and t.creattime <= to_date('"+endtime+" 23:59:59', 'yyyy-MM-dd hh24:mi:ss') ";
			}
			
			if(!"".equals(org_name)){
				sql2 +=" and P.CERT_INST_NAME = '"+org_name+"' ";
			}
			
			sql3 +=" where 1=1 ";
			if(!"".equals(batch_no)){
				sql3 +=" and t.BATCH_NO like '"+batch_no+"%' ";
			}
			
			if(!"".equals(prod_namemome)){
				sql3 +=" and t.PSC_DESC like '"+prod_namemome+"%' ";
			}
			
			if(!"".equals(design_key)){
				sql3 +=" and t.DESIGN_KEY like '"+design_key+"%' ";
			}
			if(radio.equals("1")){
				sql3 +=" and t.PHYFSTSRESULT = '1' ";
			}else if(radio.equals("0")){
				sql3 +=" and t.PHYFSTSRESULT <> '1' ";
			}
			if(radioYN.equals("1")){
				sql3 +=" and t.PHYFSTSRESULT1 = '船检合格' ";
			}else if(radioYN.equals("0")){
				sql3 +=" and t.PHYFSTSRESULT1 = '待判' ";
			}
			
			String sql ="select * from (SELECT distinct T.BILLETID MATERIAL_NO, "
						+"  '' VESSEL, "
					    +"  P.DELIVERY_STATE_CODE, "
					    +"  P.DELIVERY_STATE_DESC, "
					    +"  TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
					    +"  C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
					    +"  T.STOVENO HEAT_NO, "
					    +"  T.ROLLNUMBER BATCH_NO, "
					    +"  Z.BILLETID_JY INSPECTION_LOT, "
					    +"  T.MOTHERBOARDNUMBER BOARD_NO, "
					    +"  T.PLY THICK, "
					    +"  T.WIDTH, "
					    +"  T.LENGTH, "
					    +"  T.THEORYWEIGHT MAT_WEIGHT, "
					    +"  '4001HB1' PLINE_CODE, "
					    +"  '厚板线' PLINE_NAME, "
					    +"  P.CERT_INST_CODE CERT_INST_CODE, "
	                    +"  case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
	                    +"  from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
					    +"  P.CREATE_ID, "
					    +"  P.CREATE_NAME, "
					    +"  P.PSC, "
					    +"  P.PROD_CODE, "
					    +"  P.PROD_NAME, "
					    +"  P.STEEL_CODE, "
					    +"  P.STEEL_NAME, "
					    +"  P.STD_CODE, "
					    +"  P.STD_NAME, "
					    +"  A.ORDERNO DESIGN_KEY, "
					    +"  P.PSC_DESC, "
					    +"  case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
					    +"  case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
	                    +"  else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
					    +"  A.PACTNO 合同号, "
					    +"  (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
					    +"  (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE, to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间,"
					    +"  to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间  "
					  +" FROM KCH_TURNOFFLIST@XGCX        T, "
					  +"     SEL_PACTDETAIL_NEW@XGCX     A, "
					  +"     QCM_JHY_SAMPLE_R_ORD P, "
					  +"     ZJ_RESULT_ALL@XGCX          Z "
					+" WHERE  A.ORDERNO = T.ALLOTORDERFORM "
					+"  AND A.PSRNO = P.PSC "
	                +"  and to_char(T.PLY, 'fm990.099') = to_char(p.thick, 'fm990.099') "
					+"  and A.STEELCODE= t.DETERMINANTCARDNUMBER "
					+"  and A.STEELCODE = p.steel_code "
			        +"  and t.DETERMINANTCARDNUMBER = p.steel_code "
					+"   and t.ROLLNUMBER = p.batch_no "
					+"   AND Z.BILLETID = T.BILLETID "
					+"    and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
					+"   AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and (d.Validflag is null or d.validflag='0')) "
				    +"   AND P.CERT_INST_CODE != '5000' "
				    + " and P.CERT_INST_CODE != 'IC003' "
				    + " and P.CERT_INST_CODE != 'IC004' "
				    + " and P.CERT_INST_CODE != 'IC001'  "
					+ "   and P.CERT_INST_CODE != 'IC010'"
					+ "   and Z.BILLETID_JY like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'H%'"
					+  sql2 
					+ ")t"
					+ sql3
					
					+"union"
					+" select * from (SELECT distinct T.BILLETID MATERIAL_NO, "
					+" '' VESSEL, "
				    +" P.DELIVERY_STATE_CODE, "
				    +" P.DELIVERY_STATE_DESC, "
				    +" TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
				    +" C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
				    +" T.STOVENO HEAT_NO, "
				    +" T.ROLLNUMBER BATCH_NO, "
				    +" Z.BILLETID_JY INSPECTION_LOT, "
				    +" T.MOTHERBOARDNUMBER BOARD_NO, "
				    +" T.PLY THICK, "
				    +" T.WIDTH, "
				    +" T.LENGTH, "
				    +" T.THEORYWEIGHT MAT_WEIGHT, "
				    +" '4001HB1' PLINE_CODE, "
				    +" '厚板线' PLINE_NAME, "
				    +" P.CERT_INST_CODE CERT_INST_CODE, "
                    +" case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
                    +" from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
				    +" P.CREATE_ID, "
				    +" P.CREATE_NAME, "
				    +" P.PSC, "
				    +" P.PROD_CODE, "
				    +" P.PROD_NAME, "
				    +" P.STEEL_CODE, "
				    +" P.STEEL_NAME, "
				    +" P.STD_CODE, "
				    +" P.STD_NAME, "
				    +" A.ORDERNO DESIGN_KEY, "
				    +" P.PSC_DESC, "
				    +" case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
				    +" case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
                    +" else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
				    +" A.PACTNO 合同号, "
				    +" (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
				    +" (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE, to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间,"
				    +" to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间  "
				  +"   FROM KCH_TURNOFFLIST@XGCX        T, "
				  +"     SEL_PACTDETAIL_NEW@XGCX     A, "
				  +"     QCM_JHY_SAMPLE_R_ORD P, "
				  +"     ZJ_RESULT_ALL@XGCX          Z "
				+"  WHERE  A.ORDERNO = T.ALLOTORDERFORM "
                +"  and to_char(T.PLY, 'fm990.099') = to_char(p.thick, 'fm990.099') "
				+"  and A.STEELCODE= t.DETERMINANTCARDNUMBER "
				+"  and t.ROLLNUMBER = p.batch_no "
				+"  AND Z.BILLETID = T.BILLETID "
				+"  and p.inspection_lot= Z.BILLETID_JY "
				+"  and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
				+"  AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and (d.Validflag is null or d.validflag='0')) "
			    +"  AND P.CERT_INST_CODE != '5000' "
			    + " and P.CERT_INST_CODE != 'IC003' "
			    + " and P.CERT_INST_CODE != 'IC004' "
			    + " and (P.CERT_INST_CODE != 'IC001'  or (P.CERT_INST_CODE = 'IC001' AND LENGTH(P.THREE_MEMO)>=6) ) "
			    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社,中国船级社') "
			    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('检测中心,挪威船级社') "
			    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社') "
			    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('韩国船级社,挪威船级社') "
				+ " and P.CERT_INST_CODE != 'IC010'"
				+ " and Z.BILLETID_JY like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'H%'"
				+  sql2 
				+ ")t "+sql3
				+" and t.CERT_INST_CODE != 'IC001' "
				+" AND T.MATERIAL_NO NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where D.CREATE_TIME <= TO_DATE('2024-12-18 00:00:00','YYYY-MM-DD HH24:MI:SS') )";
	          
				StringBuffer sqlucomm = new StringBuffer();
				sqlucomm.append(sql);
				try {
					List<HashMap> listEle= mapper.query(sqlucomm.toString());
					cro.setResult(listEle);
				} catch (Exception ex) {
					cro.setV_errCode(-1);
					cro.setV_errMsg("获取数据出错"+ex.getMessage());
					SqlSession.rollback();
				}
				SqlSession.close();
			    return cro;
		}
		
		/**
		 * 查询库存数据中板线
		 * @param strDate
		 * @param endDate
		 * @param orgName
		 * @param batch
		 * @param design_key
		 * @return
		 * */
		public CoreReturnObject QueryKczInfo(String starttime,String endtime,String batch_no,String org_name,String design_key,String prod_namemome, String radio,String radioYN) {
			String sql2="";
			String sql3="";
			if (StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)) {	
				String sky=StrSky(starttime,endtime);
				if(sky.equals("FALSE")){
					SqlSession.close();
					cro.setV_errCode(-1);
					cro.setV_errMsg("查询时间不能大于半年,请核实查询时间条件!");
					return cro;
				}
			}
			if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
				sql2 +=" and t.creattime >= to_date('"+starttime+" 00:00:00', 'yyyy-MM-dd hh24:mi:ss')";
				sql2 +=" and t.creattime <= to_date('"+endtime+" 23:59:59', 'yyyy-MM-dd hh24:mi:ss') ";
			}
			if(!"".equals(org_name)){
				sql2 +=" and P.CERT_INST_NAME = '"+org_name+"' ";
			}
			
			sql3 +=" where 1=1 ";
			if(!"".equals(batch_no)){
				sql3 +=" and t.BATCH_NO like '"+batch_no+"%' ";
			}
			if(!"".equals(prod_namemome)){
				sql3 +=" and t.PSC_DESC like '"+prod_namemome+"%' ";
			}
			
			if(!"".equals(design_key)){
				sql3 +=" and t.DESIGN_KEY like '"+design_key+"%' ";
			}
			if(radio.equals("1")){
				sql3 +=" and t.PHYFSTSRESULT = '1' ";//fst_judgeresult
			}else if(radio.equals("0")){
				sql3 +=" and t.PHYFSTSRESULT <> '1' ";
			}
			if(radioYN.equals("1")){
				sql3 +=" and t.PHYFSTSRESULT1 = '船检合格' ";
			}else if(radioYN.equals("0")){
				sql3 +=" and t.PHYFSTSRESULT1 = '待判' ";
			}
			if(StringUtils.isNotBlank(starttime)&& StringUtils.isNotBlank(endtime)){
				sql3 +=" and t.创建时间 >= '"+starttime+" 00:00:00' ";
				sql3 +=" and t.创建时间 <= '"+endtime+" 24:00:00' ";
			}
						
	            String sql = "select * from ( SELECT distinct T.BILLETID MATERIAL_NO, "
							 +"      '' VESSEL, "
							 +"      P.DELIVERY_STATE_CODE, "
							 +"      P.DELIVERY_STATE_DESC, "
							 +"      TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
							 +"      C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
							 +"      T.STOVENO HEAT_NO, "
							 +"      T.ROLLNUMBER BATCH_NO, "
							 +"      Z.BILLETID_JY INSPECTION_LOT, "
							 +"      T.MOTHERBOARDNUMBER BOARD_NO, "
							 +"      T.PLY THICK, "
							 +"      T.WIDTH, "
							 +"      T.LENGTH, "
							 +"      T.THEORYWEIGHT MAT_WEIGHT, "
							 +"      '4001ZB1' PLINE_CODE, "
							 +"      '中板线' PLINE_NAME, "
							 +"      P.CERT_INST_CODE CERT_INST_CODE, "
						     +"  case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
						     +"  from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
							+"       P.CREATE_ID, "
							 +"      P.CREATE_NAME, "
							 +"      P.PSC, "
							 +"      P.PROD_CODE, "
							 +"      P.PROD_NAME, "
							 +"      P.STEEL_CODE, "
							 +"      P.STEEL_NAME, "
							 +"      P.STD_CODE, "
							 +"      P.STD_NAME, "
							 +"      A.ORDERNO DESIGN_KEY, "
							 +"     P.PSC_DESC, "
							 +"   case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
							 +"   case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
	                         +"   else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
							 +"      A.PACTNO 合同号, "
							 +"      (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
							 +"      (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE,to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间 ,"
							 + "     to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间 "
							 +" FROM KCZ_TURNOFFLIST@XGCX    T, "
							 +"      SEL_PACTDETAIL_NEW@XGCX A, "
							 +"      QCM_JHY_SAMPLE_R_ORD    P, "
							 +"      ZJ_RESULT_ALL@XGCX      Z "
							 +" WHERE A.ORDERNO = T.ORDERNO "
							 +"  AND A.PSRNO = P.PSC and T.PLY = p.thick "
							 +"   and t.ROLLNUMBER = p.batch_no "
							 +"  AND Z.BILLETID = T.BILLETID "
							+"   and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
							 +"  AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and (d.Validflag is null or d.validflag='0')) "
	                         +"  and t.ISVALID <> '0' "
							 +"   AND P.CERT_INST_CODE != '5000' "
							 + "  and P.CERT_INST_CODE != 'IC003' "
							 + " and P.CERT_INST_CODE != 'IC004' "
							 + " and P.CERT_INST_CODE != 'IC001'   "
							 + " and P.CERT_INST_CODE != 'IC010'"
							 + "  and  P.INSPECTION_LOT like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'Z%' "
							 + sql2
							 + ")t"+sql3
							 
	                      +" union "
	                      +" select * from ( SELECT distinct T.BILLETID MATERIAL_NO, "
						 +"      '' VESSEL, "
						 +"      P.DELIVERY_STATE_CODE, "
						 +"      P.DELIVERY_STATE_DESC, "
						 +"      TO_CHAR(A.SENDBEGINDATE, 'YYYY-MM-DD') DELIVERY_DATE, "
						 +"      C2N@XGCX(T.CUTSIDESTATE) CUT_TYPE, "
						 +"      T.STOVENO HEAT_NO, "
						 +"      T.ROLLNUMBER BATCH_NO, "
						 +"      Z.BILLETID_JY INSPECTION_LOT, "
						 +"      T.MOTHERBOARDNUMBER BOARD_NO, "
						 +"      T.PLY THICK, "
						 +"      T.WIDTH, "
						 +"      T.LENGTH, "
						 +"      T.THEORYWEIGHT MAT_WEIGHT, "
						 +"      '4001ZB1' PLINE_CODE, "
						 +"      '中板线' PLINE_NAME, "
						 +"      P.CERT_INST_CODE CERT_INST_CODE, "
					     +"  case when P.THREE_MEMO is not null then P.THREE_MEMO else   dbms_lob.substr((select wmsys.wm_concat(DISTINCT Org_Name) "
					     +"  from qcm_ord_design_std_pic c where c.design_key = P.DESIGN_KEY )) end CERT_INST_NAME, "
						+"       P.CREATE_ID, "
						 +"      P.CREATE_NAME, "
						 +"      P.PSC, "
						 +"      P.PROD_CODE, "
						 +"      P.PROD_NAME, "
						 +"      P.STEEL_CODE, "
						 +"      P.STEEL_NAME, "
						 +"      P.STD_CODE, "
						 +"      P.STD_NAME, "
						 +"      A.ORDERNO DESIGN_KEY, "
						 +"      P.PSC_DESC, "
						 +"   case when z.JUDGERESULT = 1 then '1' else Z.fst_judgeresult end PHYFSTSRESULT,"
						 +"   case when substr(P.DESIGN_KEY,0,2) = '19' then '非计划' "
                         +"   else decode(z.JUDGERESULT, '1', '船检合格', '待判') end PHYFSTSRESULT1, "
						 +"      A.PACTNO 合同号, "
						 +"      (select inceptcorpname from sel_inceptcorpinfo@xgcx where INCEPTCORPCODE  = A.INCEPTCORPCODE) INCEPTCORPCODE, "
						 +"      (select buyername from sel_buyerbaseinfo@xgcx where buyercode = A.BUYERCODE) BUYERCODE,to_char(t.creattime,'yyyy-MM-dd hh24:mi:ss') 创建时间 ,"
						 +"      to_char(Z.fst_judge_time, 'yyyy-MM-dd hh24:mi:ss') 初检时间 "
						 +" FROM KCZ_TURNOFFLIST@XGCX    T, "
						 +"      SEL_PACTDETAIL_NEW@XGCX A, "
						 +"      QCM_JHY_SAMPLE_R_ORD    P, "
						 +"      ZJ_RESULT_ALL@XGCX      Z "
						 +" WHERE A.ORDERNO = T.ORDERNO "
						 +"  and to_char(T.PLY, 'fm990.099') = to_char(p.thick, 'fm990.099')"
						 +"  and t.ROLLNUMBER = p.batch_no "
						 +"  AND Z.BILLETID = T.BILLETID "
						 +"  and p.inspection_lot = Z.BILLETID_JY "
						 +"  and P.DELIVERY_STATE_DESC = A.STANDSTATUS "
						 +"  AND T.BILLETID NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where d.steel_code=p.steel_code and (d.Validflag is null or d.validflag='0')) "
                         +"  and t.ISVALID <> '0' "
						 +"  AND P.CERT_INST_CODE != '5000' "
						 + " and P.CERT_INST_CODE != 'IC003' "
						 + " and P.CERT_INST_CODE != 'IC004' "
						 + " and (P.CERT_INST_CODE != 'IC001'  or (P.CERT_INST_CODE = 'IC001' AND LENGTH(P.THREE_MEMO)>=6) ) "
						    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社,中国船级社') "
						    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('检测中心,挪威船级社') "
						    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('挪威船级社') "
						    + " AND nvl(P.THREE_MEMO,'null') NOT IN ('韩国船级社,挪威船级社') "
						 + " and P.CERT_INST_CODE != 'IC010'"
						 + "  and  P.INSPECTION_LOT like T.ROLLNUMBER||'%' and T.ROLLNUMBER LIKE 'Z%' "
						 + sql2
						 + ")t"+sql3
						 +" and t.CERT_INST_CODE != 'IC001' "
						 +" AND T.MATERIAL_NO NOT IN (SELECT D.MATERIAL_NO FROM QCM_MAT_CERT_D D where D.CREATE_TIME <= TO_DATE('2024-12-18 00:00:00','YYYY-MM-DD HH24:MI:SS') )";

	        
				StringBuffer sqlucomm = new StringBuffer();
				sqlucomm.append(sql);
				//System.out.println(sql);
				try {
					List<HashMap> listEle= mapper.query(sqlucomm.toString());
					cro.setResult(listEle);
				} catch (Exception ex) {
					cro.setV_errCode(-1);
					cro.setV_errMsg("获取数据出错"+ex.getMessage());
					SqlSession.rollback();
				}
				SqlSession.close();
			    return cro;
		}

		
}