XinYu_LZMES/.svn/pristine/dc/dc5090d2967473834189ea0446e79dc3473470ae.svn-base

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