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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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; } }