package UIM; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import CoreFS.SA01.CoreIComponent; import CoreFS.SA06.CoreReturnObject; /** * 冷轧成品库钢卷出入库记录查询 * @author siy * @date 2010-08-13 */ public class UIM010210 extends CoreIComponent { /** * 查询钢卷出入库记录 * @param queryType 查询类型 0 入库 1出库 * @param list 参数集 * @return CoreReturnObject * @throws SQLException */ public CoreReturnObject queryYardList(Integer queryType,ArrayList list) throws SQLException { CoreReturnObject cro = new CoreReturnObject(); String []queryParams = (String[])list.get(0); String startTime = queryParams[0];//开始时间 String endTime = queryParams[1];//结束时间 String specStlGrd = queryParams[2];//牌号 String coilWthMin = queryParams[3];//宽度最小值 String coilWthMax = queryParams[4];//宽度最大值 String coilThkMin = queryParams[5];//厚度最小值 String coilThkMax = queryParams[6];//厚度最大值 String curLoadLoc = queryParams[7];//垛位 String coilNo = queryParams[8];//钢卷号 String shift = queryParams[9];//班次 String group = queryParams[10];//班组 String regId = queryParams[11];//作业人 String custCd = queryParams[12];//客户编号 String ordNo = queryParams[13];//订单号 String ordSeq = queryParams[14];//合同号 String ordFl = queryParams[15];//订单区分 String prodnmCd = queryParams[16];//产品类型 String stockNo = queryParams[17];//库存 String deldatefr = queryParams[18];//产品类型 String deldateto = queryParams[19];//库存 String sql = ""; //入库记录 if(0 == queryType.intValue()){ sql = "SELECT LX.*,C_PKG_UIM.GET_STDDATESTR(A.YARD_ENTRY_DTIME) YARD_ENTRY_DTIME," + "XB_PACKAGE.GF_COMNNAME_FIND('A01004' ,A.PRODNM_CD ) PRODNM_CD ," + "C_PKG_UIM.GET_STDTIMESTR(A.YARD_ENTRY_USE_TIME) CZSJ " + ",DECODE(A.YARD_ENTRY_SHIFT,'0','常白班','1','早班','2','中班','3','晚班') SHIFT " + ",DECODE(A.YARD_ENTRY_GROUP,'0','常白班','1','甲班','2','乙班','3','丙班','4','丁班') WKGROUP ," + "(SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.YARD_ENTRY_REG) CZR ,SUBSTR(A.OLD_SAMPL_NO,1,9)ZPH ,A.OLD_SAMPL_NO ," + "A.SPEC_STL_GRD ,TO_CHAR(A.INSTR_COIL_THK,'FM990.99999')||'*'||A.INSTR_COIL_WTH GUIGE ,L.YCSCCZYJ,L.YCJSCZYJ," + "TO_CHAR( A.COIL_THK,'FM990.99999')||'*'||A.COIL_WTH T_W ,A.ACT_WGT ,A.CUR_LOAD_LOC ," + "decode(A.TOT_DEC_GRD,'1','合格','2','不合格','3','外观异常','4','废品') TOT_DEC_GRD ," + "decode(A.INGR_DEC_GRD,'0','综合判定取消','1','合格','2','不合格') INGR_DEC_GRD ," + "decode(A.MATLQLTY_DEC_GRD,'0','无性能要','1','合格','2','不合格') MATLQLTY_DEC_GRD ," + "decode(A.SIZE_DEC_RST,'1','合格','2','订单外合格','3','不合格','4','废品') SIZE_DEC_RST ," + "decode(A.EXTSHAPE_DEC_GRD,'1','合格','2','订单外合格','3','次品','4','废品') EXTSHAPE_DEC_GRD ," + "DECODE(A.WGT_DEC_RST,'1','合格','2','不合格') WGT_DEC_RST ,A.ORD_NO ,a.ORD_SEQ ," + "(SELECT T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD = 'A01012' AND T.SM_CD = C.DLIV_TP) DLIV_TP ," + "(SELECT T.CUST_NM FROM TBZ00_CUSTOMER T WHERE T.CUST_CD = D.ORDCUST_CD) ORDCUST_CD ," + "SUBSTR(B.SLAB_NO,1,10) CHARGE_NO ,C.ORD_DEVLMT_DATE,C_PKG_UIM.GET_STDDATESTR(A.TRNF_DTIME) TRNF_DTIME, "+ "DECODE(A.ORD_FL,'1','订单材','2','余材') ORD_FL "+ ",DECODE(A.IN_YARD_KIND,'1','连退下线','2','重卷下线','3','连退吊销','4','重卷吊销','5','盘入连退后库','6','盘入重卷前库','7','盘入重卷后库') OP_TYPE"+ ",A.DLIV_DIRNO DLIV_DIRNO "+ ",E.H_COIL_NO1 H_COIL_NO"+ " FROM C_TBC02_COIL_COMM A ," + "TBG02_SLAB_COMM B ,TBA01_ORD_LINE C ,TBA01_ORD_COMM D ,C_TBL02_COIL_COMM E ,LZ_YCCZYJ L ,(SELECT t2.SMP_NO, "+ "MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP, 'YS000', T2.QLTY_VAL_WK, NULL)) LC_YSREL "+ //--屈服强度REL ",MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP, 'YS0.2', T2.QLTY_VAL_WK, NULL)) LC_YSRP "+//--屈服强度Rp0.2 ",MAX(DECODE(SUBSTR(T2.QLTY_CD , 1,2) , 'TE' , T2.QLTY_VAL_WK , NULL)) LC_TE "+// --抗拉强度 ",MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP, 'EL050', T2.QLTY_VAL_WK, NULL)) LC_EL50 "+//--伸长率A50 ", MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP, 'EL080', T2.QLTY_VAL_WK, NULL)) LC_EL80 "+//--伸长率A80 " from TBB02_WORK_QLTY T2, TBB01_SEQ_QLTY T4 WHERE T2.QLTY_CD = T4.QLTY_CD GROUP BY t2.SMP_NO) lx WHERE '1' = '1' " + "AND A.SLAB_NO = B.SLAB_NO(+) AND A.SMP_NO=LX.SMP_NO(+) AND A.ORD_NO = C.ORD_NO(+) AND" + " A.ORD_SEQ = C.ORD_SEQ(+) AND A.ORD_NO = D.ORD_NO(+) AND" + " SUBSTR(A.OLD_SAMPL_NO,1,12)=E.OLD_SAMPL_NO(+) AND A.OLD_SAMPL_NO = L.COIL_NO(+) AND "+ " (SUBSTR(A.YARD_ENTRY_DTIME,1,8) >= ?) AND" + " (SUBSTR(A.YARD_ENTRY_DTIME,1,8) <= ?) " + "AND A.SPEC_STL_GRD LIKE ? || '%' " + "AND A.COIL_WTH >= NVL(?, '00000') "+ "AND A.COIL_WTH <= NVL(?, '99999') "+ "AND A.COIL_THK >= NVL(?, '000') "+ "AND A.COIL_THK <= NVL(?, '999') "+ "AND (A.CUR_LOAD_LOC LIKE ? || '%' OR A.CUR_LOAD_LOC IS NULL) " + "AND A.OLD_SAMPL_NO LIKE '%' || ? || '%' " + "AND A.YARD_ENTRY_SHIFT || '&' LIKE ? || '%' " + "AND A.YARD_ENTRY_GROUP || '&' LIKE ? || '%' " + " AND (SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.YARD_ENTRY_REG) || '&' LIKE ? || '%'" + "AND D.ORDCUST_CD || '&' LIKE ? || '%' " + " AND A.ORD_NO || '&' LIKE ? || '%' " + "AND A.ORD_SEQ || '&' LIKE ? || '%' " + "AND A.ORD_FL || '&' LIKE ? || '%' " + "AND A.PRODNM_CD || '&' LIKE ? || '%' " + //"AND A.CUR_LOAD_LOC||'&' LIKE ?||'%'"+ (queryParams[17].equals("3") ? "AND (A.CUR_LOAD_LOC||'&' LIKE '3%' OR A.CUR_LOAD_LOC||'&' LIKE '4%') " : queryParams[17].equals("5") ? "AND A.CUR_LOAD_LOC||'&' LIKE '5%' " : " ") + "AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8),(SUBSTR(A.YARD_ENTRY_DTIME, 1, 8))) >= NVL(?,'0')) AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8),(SUBSTR(A.YARD_ENTRY_DTIME, 1, 8))) <= NVL(?,'99999999')) " + "order by a.YARD_ENTRY_DTIME,a.spec_stl_grd, GUIGE";//20121225 //"order by a.YARD_ENTRY_DTIME,a.spec_stl_grd,a.instr_coil_thk,a.instr_coil_wth"; } else{//出库记录 String condition = ""; if(stockNo.equals("3")){ condition = "AND A.OUT_YARD_KIND IN('1','2','4','5','6') "; }/*else if(stockNo.equals("4")){ condition = "AND A.OUT_YARD_KIND IN('1','6')"; }*/else if(stockNo.equals("5")){ condition = "AND A.OUT_YARD_KIND IN('3','7') "; } sql = "SELECT lx.*,C_PKG_UIM.GET_STDDATESTR(A.YARD_ENTRY_DTIME) YARD_ENTRY_DTIME ," + "XB_PACKAGE.GF_COMNNAME_FIND('A01004' ,A.PRODNM_CD ) PRODNM_CD ," + "C_PKG_UIM.GET_STDTIMESTR(A.TRNF_USE_TIME) CZSJ " + ",DECODE(A.TRNF_SHIFT,'0','常白班','1','早班','2','中班','3','晚班') SHIFT " + ",DECODE(A.TRNF_GROUP,'0','常白班','1','甲班','2','乙班','3','丙班','4','丁班') WKGROUP ," + "(SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.TRNF_REG) CZR ,SUBSTR(A.OLD_SAMPL_NO,1,9)ZPH ,A.OLD_SAMPL_NO ," + "A.SPEC_STL_GRD ,TO_CHAR(A.INSTR_COIL_THK,'FM990.99999')||'*'||A.INSTR_COIL_WTH GUIGE ,L.YCSCCZYJ,L.YCJSCZYJ, " + "TO_CHAR(A.COIL_THK,'FM990.99999')||'*'||A.COIL_WTH T_W ,A.ACT_WGT ,A.CUR_LOAD_LOC ," + "decode(A.TOT_DEC_GRD,'1','合格','2','不合格','3','外观异常','4','废品') TOT_DEC_GRD ," + "decode(A.INGR_DEC_GRD,'1','合格','2','不合格') INGR_DEC_GRD ," + "decode(A.MATLQLTY_DEC_GRD,'1','合格','2','不合格') MATLQLTY_DEC_GRD ," + "decode(A.SIZE_DEC_RST,'1','合格','2','不合格') SIZE_DEC_RST ," + "decode(A.EXTSHAPE_DEC_GRD,'1','合格','2','订单外合格','3','次品','4','废品') EXTSHAPE_DEC_GRD ," + "DECODE(A.WGT_DEC_RST,'1','合格','2','不合格') WGT_DEC_RST ," + "A.ORD_NO ,a.ORD_SEQ ,(SELECT T.SM_CFNM FROM TBZ00_COMMCD T " + "WHERE T.LG_CD = 'A01012' AND T.SM_CD = C.DLIV_TP) DLIV_TP ," + "(SELECT T.CUST_NM FROM TBZ00_CUSTOMER T WHERE T.CUST_CD = D.ORDCUST_CD) ORDCUST_CD ," + "SUBSTR(B.SLAB_NO,1,10) CHARGE_NO ,C.ORD_DEVLMT_DATE,C_PKG_UIM.GET_STDDATESTR(A.TRNF_DTIME) TRNF_DTIME, " + "DECODE(A.ORD_FL,'1','订单材','2','余材') ORD_FL "+ ",DECODE(A.OUT_YARD_KIND,'1','重卷上料','2','连退卷发运','3','重卷发运','4','连退返修','5','盘出连退后库','6','盘出重卷前库','7','盘出重卷后库') OP_TYPE "+ ",A.DLIV_DIRNO DLIV_DIRNO "+ ",E.H_COIL_NO1 H_COIL_NO "+ "FROM C_TBC02_COIL_COMM A ,TBG02_SLAB_COMM B ,TBA01_ORD_LINE C ,TBA01_ORD_COMM D ,C_TBL02_COIL_COMM E ,LZ_YCCZYJ L,(SELECT t2.SMP_NO, "+ "MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP, 'YS000', T2.QLTY_VAL_WK, NULL)) LC_YSREL "+ //--屈服强度REL ",MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP, 'YS0.2', T2.QLTY_VAL_WK, NULL)) LC_YSRP "+//--屈服强度Rp0.2 ",MAX(DECODE(SUBSTR(T2.QLTY_CD , 1,2) , 'TE' , T2.QLTY_VAL_WK , NULL)) LC_TE "+// --抗拉强度 ",MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP, 'EL050', T2.QLTY_VAL_WK, NULL)) LC_EL50 "+//--伸长率A50 ", MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP, 'EL080', T2.QLTY_VAL_WK, NULL)) LC_EL80 "+//--伸长率A80 " from TBB02_WORK_QLTY T2, TBB01_SEQ_QLTY T4 WHERE T2.QLTY_CD = T4.QLTY_CD GROUP BY t2.SMP_NO) lx " + "WHERE '1' = '1' AND A.SLAB_NO = B.SLAB_NO(+) AND A.SMP_NO=LX.SMP_NO(+) AND A.ORD_NO = C.ORD_NO(+) " + "AND SUBSTR(A.OLD_SAMPL_NO,1,12)=E.OLD_SAMPL_NO(+) AND A.OLD_SAMPL_NO = L.COIL_NO(+)"+ "AND A.ORD_SEQ = C.ORD_SEQ(+) AND A.ORD_NO = D.ORD_NO(+) and a.COIL_STAT = '3' AND (SUBSTR(A.TRNF_DTIME,1,8) >= ?) " + "AND (SUBSTR(A.TRNF_DTIME,1,8) <= ?) " + "AND A.SPEC_STL_GRD LIKE ? || '%' " + "AND A.COIL_WTH >= NVL(?, '00000') "+ "AND A.COIL_WTH <= NVL(?, '99999') "+ "AND A.COIL_THK >= NVL(?, '000') "+ "AND A.COIL_THK <= NVL(?, '999') "+ "AND (A.CUR_LOAD_LOC LIKE ? || '%' OR A.CUR_LOAD_LOC IS NULL) " + "AND A.OLD_SAMPL_NO LIKE '%' || ? || '%' " + "AND A.TRNF_SHIFT || '&' LIKE ? || '%' " + "AND A.TRNF_GROUP || '&' LIKE ? || '%' " + " AND (SELECT USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.YARD_ENTRY_REG) || '&' LIKE ? || '%'" + "AND D.ORDCUST_CD || '&' LIKE ? || '%' " + " AND A.ORD_NO || '&' LIKE ? || '%' " + "AND A.ORD_SEQ || '&' LIKE ? || '%' " + "AND A.ORD_FL || '&' LIKE ? || '%' " + "AND A.PRODNM_CD || '&' LIKE ? || '%' " + condition+"AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8),(SUBSTR(A.TRNF_DTIME, 1, 8))) >= NVL(?,'0')) AND (NVL(SUBSTR(C.ORD_DEVLMT_DATE, 1, 8),(SUBSTR(A.TRNF_DTIME, 1, 8))) <= NVL(?,'99999999')) " + "order by a.TRNF_Dtime,a.spec_stl_grd, GUIGE";//20121225 //"order by a.TRNF_Dtime,a.spec_stl_grd,a.instr_coil_thk,a.instr_coil_wth"; } cro = this.getDao("KgDao").ExcuteQuery(sql,new Object[]{startTime, endTime, specStlGrd, coilWthMin, coilWthMax, coilThkMin, coilThkMax, curLoadLoc, coilNo, shift, group, regId, custCd, ordNo, ordSeq, ordFl, prodnmCd,deldatefr,deldateto}); return cro; } }